Automated deployment of mixed SSIS / DLL solution

Question

We currently have a solution developed using SSIS / C#. The SSIS package (amongst other things) has a script task that uses logic developed in the class libraries. This functionality needs to remain separate from the SSIS package.

Because we are using an SSIS package I understand that the compiled DLL's need to be deployed to the GAC, and then referenced from the script task. However this is creating a deployment problem for us.

Our automated deployment tool (rightly) automatically increments the version numbers of the DLL's, which are then published to the GAC. However this breaks the SSIS package, as it will try and access the DLL's based on the version number they were published to the development machine GAC as.

The only solution we have to this is to get the compiled DLL's, manualy modify the SSIS package script task and then publish the package.

It seems like there must be a better way of doing this - has anyone encountered this problem and come up with a better solution? Or is there something fundamental in our approach we need to change (beyond eliminating the need for the DLL's)?

Thanks!

Answer 1

Well, after much research I never really came up with a satisfactory solution for this. In the end the closest I could get was a solution where I loaded my references dynamically:

    Dim rsAssembly As Assembly = Assembly.LoadFile("path from config file")
    Dim rsType As Type = rsAssembly.GetType("class name from config file")
    Dim obj As Object = Activator.CreateInstance(rsType)

This allowed me to create the object I required (although worth noting that any other dependant references also need to by dynamically load or part of the GAC, although at least without the dependancy on the version number).

Posted here for future seekers, but if anyone comes up with something better I would still be very curious to know how you resolved it - post here and I will credit you with the answer :)

Answer 2

In my case, just calling Assembly.LoadFile didn't work. What did work was this approach:

Call this in the class constructor:

AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);

And then have this method, with all the dlls you need:

 private Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
 {
     if (args.Name.Contains("libName.dll"))
         return Assembly.LoadFile(System.IO.Path.Combine(_libraryFolder, "libName.dll"));

     if (args.Name.Contains("libName2.dll"))
         return Assembly.LoadFile(System.IO.Path.Combine(_libraryFolder, "libName2.dll"));

     return null;
 }

If they are a lot, you may want to refactor that with a List<string>.

Answer 3

I have noticed similar issues with our SSIS/C# mix. We also rely on an external (to SSIS) dll. In our case the DLL had to be copied to the 100/DTS/Binn directory to allow the SSIS package to work from within Visual Studio however when we attempt to run the package using the SQL Package Execution Utility we get an error to the affect that the file could not be found. It does not seem to indicate a version issue so much as a difference in PATH between the Visual Studio and the Package Execution Utility. Even running the package without debug from Visual Studio works. I am going to look into the version issue to see if maybe that is the core complaint on our system but from memory I think it was a file not found type error that affected us. We are using MSSQL 2008 if that makes a difference.