I learned today that any .NET assembly you want to use from an SSIS script task must be in the GAC. Not only that, I learned that any other .NET assemblies that assembly references must also be in the GAC.
The most fun thing I learned, though, is that the Visual Studio for Applications IDE used for the script task requires that any DLLs you want to reference must not only be in the GAC, but must be physically copied to the appropriate .NET framework folder, which on my machine was C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Ugggh. And don't forget to copy all of the other assemblies referenced by that assemby also (and don't forget to put those in the GAC). Double ugggh. (Thanks to Jamie Thomson for the heads up on this one [1]. As he points out, this requirement to copy the DLLs is only for development machines; a test or production deployment will find them in the GAC as expected.)
If you're trying to instantiate a .NET class from a script task and it's not in the GAC, you'll receive an error like this:
Could not load file or assembly 'foo.foo' or one of its dependencies. The system cannot find the file specified.
Update 1: something else I learned: if you recompile a DLL that you're using from a script task and update it in the GAC, you have to open up the VSA window for each script task to refresh the reference. Otherwise you will receive an error ing the first script that uses the changed assembly that the assembly could not be loaded. It's possible that it's the incrementing of the version number of the assembly that throws it off, so it may be possible to alleviate this problem during the dev-unit test cycle by not incrementing version numbers on recompile. Update 2: I just now successfully recompiled with an incremented version number, updated the GAC. and ran two script tasks without having to update the tasks as described above...hmmmm...I swear I couldn't get that to work before... Update 2: Yep, sure enough, the very next time I recompiled I ran into the error quoted above. Opening up each script task as described above fixed it.
Update 3: I stumbled across this article that has a nice explanation and illustration of how to set up, reference, and use a .NET assembly from an SSIS script task [2]. (My link is to page 2 of the three page article).
Update 4: I can't confirm this 100% yet, but I believe we have learned that if you are turning off the PreCompile option on your script task in order to keep the package size down then you need to have your DLLs in the .NET Framework folder *even in production*! That's right, when you deploy your package to production, copy those suckers to the framework folder in production, just like on your dev machine. I presume this is because the SSIS engine needs the help of something external to do the compiling at runtime and that this external something requires this. Methinks the integration between SSIS and this ".NET for Applications" engine is not as tight as it could be. (June 13, 06)
Comments welcome.
Hope that helps,
Dan