Script Component in SSIS

Script Component is a component available in DataFlowTask. Using a script component you can execute custom code as part of the DataFlowTask. How to use the 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.

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.
 
script component type 
 
Step 2 - Add derived columns and create a derived class called "ValidSSn" and the default value will be false.
 
Derived Column Transformation Editor 
 
Step 3 - Connect the input type to the script component as shown in the picture.
 
Connect the input type to the script component 
 
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.
 
Script Tranformation Editor 
 
Step 5 - Click the Edit Script option in the Script tab.
 
Edit Script 
 
Step 6 - Go to the following function in the script component.
  1. public override void Input0_ProcessInputRow(Input0Buffer Row)  
  2. {  
  3. } 
Step 7 - Add the SSN validation logic here.
 
Import the namespace for Regular Expressions.
  1. using System.Text.RegularExpressions;  
Step 8 -  Create the RegEx pattern as shown below.
  1. if (!Row.SSN_IsNull)  
  2. {  
  3.     Regex SSNRegEx = new Regex(@"^\d{3}-\d{2}-\d{4}$");  
  4.     Row.ValidSSN = SSNRegEx.IsMatch(Row.SSN);  
  5. } 
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 .
 
Conditional Split Transformation Editor 
 
Step 11 - Add the destination component for the valid ssn and invalid ssn outputs.
 
valid and invalid ssn outputs 
 
Step 12 - Execute the package and see the output in the corresponding files.
 
Execute ssis package 
 
In the source, I have the SSN numbers like:
123-12-1234 (valid) and 123-123-123 (Invalid) SSN number.