Create a custom assembly in SSIS

Integration Services custom assemblies are awesome!

I am working on a large project with a fairly sizeable development team for a Dallas/Ft Worth based company, and the old DBA left some very nice c# console application code which was, I'm guessing, originally designed to extend DTS. Even thought he carried this functionality to Integration Services, some or most of this functionality is now native to SSIS. (Remember that DTS didn't have all of the nice features in SQL 2005 SSIS unless you wrote half-baked Active-X tasks which I was and am very much against in for a number of reasons, and therefore made sense to simply move those methods out to an executable). Using the old code wrapped in an "Execute Process Task" is fine; the problem is, what happens when you have to reuse those cool code functions over and over again in other packages, projects, or solutions? The solution here was to point to copies of the executables in separate folders...now the neat idea becomes just another thing to manage. I don't know about you, but I am all for not managing things scattered all over the place as such.  Therefore, as an alternate solution, you can create a custom assembly in SSIS for all of your methods!

Let's go over the steps to do this - surprisingly simple and very straightforward. Click on the below pictures for a larger image.

1. Write/compile your code - I created a simple one in c# for this example. It does nothing more than output a string message. Use your creativity here...maybe a heavy mathematical calculation? Regex? Custom string formatting? Here's what mine looks like. Take note of the method name and namespace as these will be referenced later.

2. Strong Name key - Use the Visual Studio 2005 Command Prompt to create a strong name key. Use sn at the command prompt to create a new one, and specify the folder where you want it to be dropped. Here, I create one and store it in the c:\ETL folder. Use the help for additional switches and configuration options.

Go in to your project and add this key under project properties. Note that key.snk is already in picture 1, above.

3. GAC - GAC this baby. You can use one of two methods: 1) just drop the .dll into c:\Windows\assembly, or 2) use the gacutil executable. Remember that if doing it this way, you'll want to use the correct version of the gacutil for the version of .Net platform that you are developing on.

4. Add reference to project - In SSIS, create a script task, and add the reference like so, on the left, and write code to call the method. My minimal example, again, simply throws a message out. For this, remember that you have to move the .dll into the .Net folder for the version you are working. In the past, SQL Server wanted "extended" functionality in a certain folder, and still does. You can't browse for referenced objects.

5. Test! - Run your SSIS package and there you go - a custom assembly created for SQL Server Integration Services. Word up!

------------------------------------

 I know what you're thinking. You understood that only SELECT statements were required for this job.