SSIS, C#, and Class Libraries February 4, 2010Posted by Phill in Other Stuff.
Tags: c#, class library, sql server, ssis, visual studio
The past couple of weeks I’ve not been working in Java, I’ve actually been using SQL Server Integration Services. We are in the process of converting an old C++ based application to Java, with a SQL Server backend. Some of the old export processes that were previously written in C++ we have decided to re-write using SSIS.
I won’t bore you with the details, but I do want to blog about the way C# is used in SSIS. You can create ‘Script Tasks’ which basically run a piece of code for you. You can write it in VB.NET or C#.
Due to the particular export I was running, a lot of the logic needed to go into the C# code. The issue is, SSIS doesn’t make it easy for you to re-use code: essentially, when you create a script task you are creating a whole new C# project. This isn’t good.
What I did (after taking some advice from Stack Overflow), is create a class library. Being new to SSIS and the world of C# in general, I had to do a fair bit of trial and error to get it working. The best tutorial I found was here. Those steps were enough to get me started.
There are a few other things to mention as well:
- For some reason, SSIS 2008 seems to be stuck in the world of .NET 2.0. I don’t know why, but that is why you have to copy the .dll to the .NET 2.x folder. You will probably also need to set the project properties (of your class library project) to .NET 2.0.
- There seem to be two different ways of accessing SSIS variables from within a script, depending on whether your script is part of a Data Flow Task or not. If it’s part of a Data Flow Task, you access them via the ‘Variables’ member – easy enough. (Although remember to set them up as read-only or read-write variables in the task properties first). If your script task isn’t part of a data flow task, you will need to use the Dts variable. This confused me at first!
- For some reason, rebuilding the Class Library package in Visual Studio seems to mean the Script Tasks using it also need to be recompiled. Update: I’ve found out why – if the version increments, you will need to rebuild the dependent projects. To stop this, you just need to set the class library version to be constant. This was actually the default in my project, but I’d changed it! You can do it from project properties -> Application -> Assembly Information dialog.
In general, it’s been a learning experience – worth doing though! I will hopefully update a bit more when I’ve had a chance to use it more…