Automated deployment of mixed SSIS / DLL solution

Question

Hi All,

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

Are you absolutely sure that those shared DLLs must be deployed to the GAC? If they are in the same folder as the SSIS package, they should be discoverable by the framework without the need to add them to the GAC.

Is there no way to update your build system to avoid the version number change? If the code of those assemblies is not changing, there is no need to update the version number.

If you can't avoid the version increment, the other alternative is to build a policy file along with the shared assemblies and use that to "redirect" the SSIS package to the new version with each build.

Answer 2

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 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.