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:
Chuck some code in
Rename your class to something useful (I called it StringUtils here) and add your clever bit of code in:
- using System;
- using System.Collections.Generic;
- using System.Text;
- namespace SSISClassLib {
- public class StringUtils {
- public static string InitCap(string input) {
- string s = input.ToString();
- s=s.Substring(0,1).ToUpper()+s.Substring(1);
- int len = s.Length;
- int pos = s.IndexOf(' ');
- while(pos!=-1 && pos < (len-1)) {
- if(pos == (len -1)) {
- s = s.Substring(0,pos) + s.Substring(pos + 1,1).ToUpper() + s.Substring(pos + 1);
- } else {
- s = s.Substring(0,pos + 1) + s.Substring(pos + 1,1).ToUpper() + s.Substring(pos + 2);
- }
- pos = s.IndexOf(' ',pos+1);
- }
- return s;
- }
- }
- }
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:
- C:>sn -k Keyfile.snk
- Microsoft (R) .NET Framework Strong Name Utility Version 2.0.50727.42
- Copyright (c) Microsoft Corporation. All rights reserved.
- 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:
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:
Signing the assembly (done that above)
Copy the physical .dll file to the target machine's .NET framework directory
Install the assemply to the GAC
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.
- : Uninstall the previous assembly from the GAC
- "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /u $(TargetName)
Enter this for the Post-build event:
- : Install the assembly to the GAC
- "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /i "$(TargetPath)"
- : Copy to directory where SSIS can read it
- 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:
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:
The input and the output column names are added using the Add Column button to get the result shown:
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:
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:
- Imports System
- Imports System.Data
- Imports System.Math
- Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
- Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
- Imports SSISClassLib.StringUtils
- Public Class ScriptMain
- Inherits UserComponent
- Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
- Row.TownsInitCap = InitCap(Row.Towns)
- End Sub
- 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.