SQL Server Integration Services is a framework for developing ETL solutions based on the .NET Framework. The Script task component provides ability to perform tasks that are not available in the built-in tasks and transformations. The Script task component also allows us to combine tasks in one script instead of using multiple tasks and transformations.
If you need to process data on a row-by-row basis, you could use the Data Flow Script component instead of the Script task. The usage of SSIS Script components provides us a lot of flexibility. For example, if you want to assess the reasonableness of an order amount and skip data rows that have high or low amount, one of the options is to use a Data Flow Script component. The complexity of business rules used in a script component is limited with the C# language features.
This powerful tool gives us the ability to use third-party libraries as well as our own. In the article below we will describe the process of adding an external library to SSIS Script task component.
Create valid DLL
To describe the process of adding an external library lets create a simple C# class library that will contain a simple enumeration.
We will provide step-by-step instructions for you. Graphical information is based on the Visual Studio 2010, but in the Visual Studio 2012 its look very similar. To do this you need to:
1. Create Class library C# project
Run Microsoft Visual Studio. In a “New Project” form choose ”Visual C#”–>”Windows”. You can find it in a “Installed Templates”. After that, choose “Class Library” and set the “Name”, in our case – “EnumContainer”. Also you can specify the path to the projects folder. If you want to keep each project in own folder, don’t forget to set “Create directory for solution” checkbox.
2. Add a simple enumeration
In the code editor window add a class “Colors” according to the figure below.
3. Sign an assembly
We should sign an assembly in order to install it in your global assembly cache (GAC). It’s because only assemblies from the GAC can be referenced. To do so, access the “Signing page” by selecting a project node in “Solution Explorer”, and then, on the “Project menu”, clicking “Properties”. When the “Project Designer” appears, click the “Signing tab”. As a result you will see a form similar to figure below. Set “Sign the assembly” and choose appropriate strong name key file.
4. Register an assembly to GAC
After the assembly signing we have to register it to GAC. There are two ways to install an assembly into the global assembly cache: using the Global Assembly Cache tool (Gacutil.exe) or using Microsoft Windows Installer. In our example we will use the first method. To do this run this command in Visual Studio Command Prompt:
C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC>gacutil.exe –i “D:\Projects\EnumContainer\bin\Release\EnumContainer.dll”
Add assembly reference to SSIS script task
Now we have the registered assembly (dll file) which we will use in SSIS script task. Start Visual Studio and create a SSIS project. If Visual Studio is still open, close it first because it caches the GAC on start.
1. To be able to use your assembly in the SSIS script task you need to copy it to the common folder for keeping assemblies. The path to this folder may vary depending on the version of SQL Server installed. In our case it is “C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies”.
2. After that, go to SSIS Script task and press Edit Script. In the window opened we need to add a reference to our DLL. To do so click right mouse button on “References” in a “Solution Explorer” and choose the “Add Reference…” option.
3. In an opened window shown below choose the “Browse” tab and select your DLL. In our example it is “EnumCounter.dll”.
4. On the next step we should add our assembly into the using list. To do this just add “using EnumContainer;” string to your script. The result should be similar with the figure below.
5. Finally, after this we can use our enumeration in SSIS Script task. We provide you the example of such code.
public void> Main()
// TODO: Add your code here
int colorID = (int)Colors.Red;
Dts.TaskResult = (int)ScriptResults.Success;
Making changes to the assembly
During the project growth we can make a lot of changes to the assembly. Most common task is to see these changes at design time and at the runtime.
To see assembly changes in the Script task at design time you need just to rebuild the project and replace the file you’ve choose at “Add assembly reference to SSIS script task: Step 6”.
To see assembly changes during the runtime you need to build the project and reinstall your DLL into the GAC.
This approach allows us to share the common C# code between many SSIS Script task components, avoid code duplication and code discrepancies. Also we can make changes to the part of functionality without redeploying SSIS project. All changes will be counted by reinstalling DLL on the SSIS server.
About the Author
Oleksii Shabrov: “I work as a database developer for more than 9 years. I love my job and try to improve in this area, as well as learn new areas not associated with the databases. My life outside of work and my work are closely intertwined. I love to travel, and business trips has allowed me to discover new countries. I love cheerful companies, and the IT sphere gave me great friends. My hobby is table tennis and even at work I can play it.”