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.