Calling SSIS that uses a DLL with SQL Server Agent requires the DLL to be installed on the calling machine and not the machine where pacakge is installed

Question

We have a SSIS 2005 package that is installed on a central server and is called from multiple locations. This package uses a script task to call a .NET DLL which I wrote in c# and installed into the GAC on the central server. When I call the SSIS package from that server on which the package is installed everything is fine.

When I call the package from a remote server using SQL Server Agent, the job fails reporting that it cannot find the DLL.

Just to test out what is happening I installed the dll on the remote server and the package succeeded. So it appears that although the package is installed on one machine, when it is called from another using SQL Server agend it actually executes on the calling machine and it is the calling machine that must satisfy all the dependencies.

This package is going to be called from dozens of servers, many of which I do not have control over.

Is there a way in which I can, install, configure, compile, call or otherwise do something to the way this package is built or executed so that it will call the DLL from the GAC on the machine where the package is installed?

Answer 1

Unfortunately, you will need to change your design, since SSIS package storage is just that - storage. Execution always takes place on the machine from which the package is called, and all references are treated as relative to that machine.

One option is to add a task to the SSIS package which copies and registers the DLL in the GAC of the calling machine - but if you do not have control over some of the executing machines, there is no guarantee the executing SQL Agent account will have sufficient rights to register a DLL.

Another solution would be to convert the DLL code into a script task inside the SSIS package. This would mean converting the code from C# to VB, and may be non-trivial depending on the detail of your code.

Without more details of the purpose of the package and the functionality of the DLL it's difficult to evaluate other alternatives, but you could consider whether it would be possible to parameterise the package to enable it always to run from the storage server.