Calling external DLLs from a Script Task in SSIS 2012

The 2012 version of SQL Server has brought with it a number of new features, not least the new, Visual Studio 2010 shell-based Management Studio. However, the changes are more than just aesthetic, as I found out when I came to try and upgrade some SQL Server Integration Studio (SSIS) packages from SQL server 2008 to 2012. Most packages upgraded fine, but there were some issues when I tried to upgrade any packages that used Script Tasks containing references to external DLLs.

The Script Task is the most versatile component of SSIS.  Using either VB.NET or C# code, you can basically do anything you want with it, from assigning variables, loading/writing data, or even calling web services.  The potential of the Script Task is virtually limitless, thanks to the ability to reference external DLLs.

With SQL 2008, referencing an external DLL was relatively simple, if a little convoluted.

You’d create your external class library, making sure that you’ve signed the assembly with a strong-named key file and set the correct target framework and CPU (usually .NET 2 and relevant choice of “x86” or “Any CPU”).  You’d then take this DLL, add it to the Global Assembly Cache (GAC), and the DTS folder with your SQL install (or alternatively the .NET Framework folder).  Reference the assembly in your Script Task and you’re there!

Links has the details of the pre-SQL 2012 setup on his site, so I won’t get into the details here.

Where’s the GAC gone?

Now, as well as updating the shell and making SSIS 2012 all pretty, the major factor in terms of Script Tasks is that the default version of the .NET Framework has been updated to .NET 4.  One of the changes to .NET 4 was the addition of a brand new GAC, that sits alongside the previous one due to updates to the Common Language Runtime (CLR). There’s a good discussion of this over on Stackoverflow.

It should be possible to change the Target Framework and Target CPU of your Script Task (as with any Visual Studio project) to match the version and CPU of your external DLL, however when I tried this, I ran into an unexpected bug.  It seems that the editor in SSIS 2012 has a little bug with the UI, specifically with the Target Framework dropdown.  While it changes the target framework as expected, this closes the project properties window and when you re-open it, it will always show .NET 4 again.  This results in a situation where you can add .NET 2 DLLs to the project, but as soon as the package is executed, it throws a reflection error.

So, without manually editing the project XML file (which is an option but remember they’re temporary), the easiest option seems to be to upgrade our external DLL to .NET 4 (it’s better anyway), and deploy to the new folders.

Deploy! Deploy! Deploy!

Once your external DLL has been rebuilt as a better, stronger, faster .NET 4 class library, and signed as with the previous version, you can deploy it to the new GAC, now located in:

C:\WindowsMicrosoft.NETassemblyGAC_[CPU version]

In addition, to run this locally, you’ll also need to add the DLL to the SQL Server assemblies folder, which has also moved slightly since 2008.  It’s now in:

C:\Program Files (x86)Microsoft SQL Server110SDKAssemblies

Note that at time of writing, even with any “Any CPU” DLL and on a 64-bit PC, I had to deploy the assembly into the Program Files (x86)Microsoft SQL Server directory, not the Program FilesMicrosoft SQL Server one.

And that should be it!  Follow the same setup on your server* and you shouldn’t have any issues running your SSIS package.

I had further problems installing to the GAC on the server due to not having GACUtil installed, so had to download the Windows 7.1 SDK to add my assemblies to the GAC.

Has anyone had similar troubles getting custom assemblies to work in SSIS 2012?  Have you found a quicker and easier workaround?  Let me know in the comments below!