Create and use a C# Assembly for SSIS - Page 3

Article Index
Create and use a C# Assembly for SSIS
Page 2
Page 3
All Pages

 

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.