Create and use a C# Assembly for SSIS

Print
Details

Here's how to create a C# class library in Visual Studio and invoke it from an SSIS script component (using just the teensy weensiest bit of VB.Net code):

 

Creationalist Assemblage

(Huh huh... see my article on the Flying Spaghetti Monster) 

 

When you have become weary of the world and are tired of crap programming languages like VB, but you need to write a complicated piece of logic for SSIS, you may consider going down the route of programming it in an eloquent programming like C#.

 

Download the example code.

 

Create a C# class library project

 You're using Visual Studio 2005, right? Create a new project - go: File -> New -> Project and choose the type of project that you want to build:

 

Image

 

Chuck some code in

 Rename your class to something useful (I called it StringUtils here) and add your clever bit of code in:

 

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4.  
  5. namespace SSISClassLib {
  6.   public class StringUtils {
  7.     public static string InitCap(string input) {
  8.       string s = input.ToString();
  9.       s=s.Substring(0,1).ToUpper()+s.Substring(1);
  10.       int len = s.Length;
  11.       int pos = s.IndexOf(' ');
  12.       while(pos!=-1 && pos < (len-1)) {
  13.         if(pos == (len -1)) {
  14.           s = s.Substring(0,pos) + s.Substring(pos + 1,1).ToUpper() + s.Substring(pos + 1);
  15.         } else {
  16.           s = s.Substring(0,pos + 1) + s.Substring(pos + 1,1).ToUpper() + s.Substring(pos + 2);
  17.         }
  18.         pos = s.IndexOf(' ',pos+1);
  19.       }
  20.       return s;
  21.     }
  22.   }
  23. }

 


 

Sign, Build and Deploy

 

Create a GAC signature

 Skip this step if you already have a project-wide GAC signature file available with which to sign your assebly with.

 

Open your console and create a signature file using the sn (StrongName) command, which is installed with the .Net SDK:

 

  1. C:>sn -k Keyfile.snk
  2.  
  3. Microsoft (R) .NET Framework Strong Name Utility  Version 2.0.50727.42
  4. Copyright (c) Microsoft Corporation.  All rights reserved.
  5.  
  6. Key pair written to Keyfile.snk

  

Add the signature file to your project

 Add your signature file to your project just like any other existing file:

Image

 

Sign your assembly

 In addition to adding the file to your project, you also need to specify that you would like to sign your project.  This is done from the Project Properties screen. Select the "Signing" tab, choose to "Sign the assemby" and select your signature file from the dropdown box.

  

Configure the deployment

 At this stage, your assembly is ready to build, but it will not be recognized by SSIS until it is properly deployed in the GAC. 

 

In summary, the deployment consists of 3 steps:

 

 

The best place to do the last two steps is to script them in the project's Pre- and Post-build  event handlers.

 

Click on the "Build Events" tab and enter the following code for the Pre-build event. The .NET framework directory for Visual Studio 2005 is C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727, for Visual Studio 2008 it is C:\Windows\Microsoft.NET\Framework\v3.5. These are the only directories that SSIS can reference. Bare this in mind for the code below.

 

  1. : Uninstall the previous assembly from the GAC
  2. "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /u $(TargetName)

 

Enter this for the Post-build event:

 

  1. : Install the assembly to the GAC
  2. "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /i "$(TargetPath)"
  3. : Copy to directory where SSIS can read it
  4. copy "$(TargetPath)" "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727" /y

 

Belts&Braces Note: 

 

I specify the fully-qualfied path for gacutil.exe here since it is not by default on your path. Ideally though, C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin (or wherever gacutil.exe lives, depending on your version of Visual Studio or Microsoft SDK) should be on your path.

 

Build and deploy the Assembly!

 Press Shift-Ctrl-B. (Learn to use  keyboard shortcuts in Visual Studio!)

 


 

Plating it up: How to use the C# assembly in SSIS

 You now have a nice, shiny-new C# assembly sitting pretty in the GAC, all signed and sealed. The following shows you how to use it inside an SSIS Script component in an SSIS dataflow (as opposed to using it in a Script task in the SSIS project's control flow, which is similar) 

 

Create a new Script component in your SSIS project

 Since we created a C# 'library' that InitCaps a string, we can correct-case a collection of british town names, e.g. convert  bury st edmonds to Bury St Edmonds. We therefore need to create a Script component of the Transformation variety to test our hard work:

 

Image

 

Connect up the data source and data sink

 For the sake of keeping this example portable, we use flat files for data source and sink. This is how we connect the input data flows to the Script componen:

 

Image

 

The input and the output column names are added using the Add Column button to get the result shown:

 

Image

 

When this is done, connect the output to the Script component to the data sink.

 

Reference the installed C# Assembly

 Next, open the VBA script editor (Go: Script -> Design Script...) and add a reference to the C# assembly. From the menu SSIS project, go: Project->Add Reference, and select the C# assembly from the list. Hit the Add button. This is what you should end up with: 

 

Image

 

Note: Adding a reference does not work if you do it through the Object Browser in the VBA editor.

 

Hit OK and start coding. 

 

Edit the Script component

 Now the small bit of VB coding starts. Only add the bits that are highlighted:

 

  1. Imports System
  2. Imports System.Data
  3. Imports System.Math
  4. Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
  5. Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
  6. Imports SSISClassLib.StringUtils
  7.  
  8. Public Class ScriptMain
  9.     Inherits UserComponent
  10.  
  11.     Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  12.         Row.TownsInitCap = InitCap(Row.Towns)
  13.     End Sub
  14. End Class

 

Close the editor when done. This causes the code to be saved.

 

Run the package

 Watch the pretty little boxes go yellow and then green on the SSIS screen. Wheee!

 You shoud end up with an output file of correctly-cased town names.