Script Component in SSIS

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.

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

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

 

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.

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

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 .

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

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

In the source, I have the SSN numbers like:

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