Reader Level:
ARTICLE

Script Component in SSIS

On May 20, 2011 Articles | SQL Server
How to use the Script Component in SSIS.
  • 0
  • 0
  • 18696
Download Files:
 

What is Script Component ?

Script Component is a component available in DataFlowTask. Using a script component you can execute custom code as part of the DataFlowTask.

When to use a Script Component?

  • To implement the Business Rules

  • To implement custom validation logic like SSN validation.

  • Merging two or more column values and process the results.

Demo: how to do SSN validation using a Script component?

Step 1:  Drag and Drop the Script component, select the Transformation option in the script component type.

sc1.png

Step 2: Add derived columns and create a derived class called "ValidSSn" and the default value will be false.

sc2.png

 Step 3: Connect the input type to the script component as shown in the picture.

sc3.png
 

Step 4: Right-click the Script component and select the columns that you want. Select the SSN column as ReadOnly and ValidSSN column as ReadWrite. In this ValidSSN column only, the script component updates the Boolean flag. If it is a validssn then it will be "true" otherwise it will be false.

sc4.png

Step 5: Click the EditScript option in the Script tab.

sc5.png

Step 6: Go to the following function in the script component.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
      
    }

Step 7: Add the SSN validation logic here.

Import the namespace for Regular Expressions.

using System.Text.RegularExpressions;

Step 8:  Create the RegEx pattern as shown below.

if (!Row.SSN_IsNull)
        {
            Regex SSNRegEx = new Regex(@"^\d{3}-\d{2}-\d{4}$");
            Row.ValidSSN = SSNRegEx.IsMatch(Row.SSN);
        }

Step 9: Build the source code and exit from the code window; press ok in the Script component Editor.

Step 10: Place a conditional split component to just separate the validssn records and invalid ssn records.

Create two outputNames; for the first one add the condtion like [ValidSSN]==true and for the invalid ssn [ValidSSN] == false .

sc10.png

Step 11: Add the destination component for the validssn and invalidss outputs.

sc11.png

Step 12 : Execute the package and see the output in the corresponding files.

sc12.png

In the source, I have the SSN numbers like:

123-12-1234 (valid) and 123-123-123 (Invalid) SSN number.

COMMENT USING

Trending up