Brian Knight, SQL Server MVP

How to use a Script Transformation

Using a Script transformation is a handy way to extend what transforms are available to you. For example, picture social security data that goes into your Script transformation unencrypted and then out encrypted. Typically, you can also use this type of custom script to extend what a Derived Column task can do. If you have the choice between the two though, always choose a Derived Column.

A good extension of a Derived Column transformation into a Script transformation would be where you need to replace certain text with other text. If you have only two options, you could use the REPLACE expression inside a Derived Column transform. When you go above two, the Replace expression becomes less elegant. So, in comes the Script transform.

After connecting the Script transform to the data flow, go to the Input Column task for the transform and check the columns that will be sent into the script as an input. Next, go to the Inputs and Outputs tab and click Add Output. Name the columns that you wish to output. I’m going to call this one OutputValue for this example.  Next, click Design Script.

In this script you can see that first we localize the variable. Because I can’t predict whether the user inputted mixed, lower or upper case, I use the UCASE function to upper case the input value for comparison purposes. The word InputValue in Row.InputValue would be replaced with whatever you checked in the input screen. Then, the main thing to remember is Row.OutputValue will set the OutputValue output we set earlier will be set to the new value. So this code will take the input of whatever the color is and translate it to the hex value. Then, the OutputValue output will be presented to the pipeline and can be consumed by the destination or the next transform in the pipeline.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim HexValue As String

Select Case UCase(Row.InputValue)
   Case "RED"
      HexValue = "#FF0000"
   Case "YELLOW"
      HexValue = "#FFFF00"
   Case "GREEN"
      HexValue = "#008000"
End Select

Row.OutputValue = HexValue
End Sub
End Class 

-- Brian Knight