Home > SSIS > SSIS – Alternate implementation of Business rule validation using Script Component

SSIS – Alternate implementation of Business rule validation using Script Component

In any ETL implementations, one of the common scenario is to checking business rule validation and handling error records to get the data corrections.
Here planning to discuss one simple way of business validation implementation using script component.
Created a package with two dataflows, one using SSIS components while another one with only script component. The package needs to log the records into LogError tables with Error message as well as needs to transfer all records to the destination.

Step 1: Implementation using CS, MCL and UNL
1. Source records has 2 rows with Person_ID = -1 OR Location_SID = -1
2. Conditional Split used to split the records
3. Derive the Validation message to Error records
4. Appended the records with Error Message and again Union All with Original record set
5. It logged all Error records to Log target and transferred to target destination with corresponding validation messages

Step 2: Implementation using Script Component
1. Script component takes input records and do the logic on data then provide the two output sets for Log target and target as

Step 3: Script Component setup
1. Select all rows as input with Usage Type: ReadOnly

2. Create two outputs i.e. All and Unknown and set the ExclusionGroup and SynchrousInputID options to map the input columns to output columns automatically i.e. this will avoid the manual creation of output columns

3. Create a row for each output to assign the Business Rule description

4. Implement the logic inside Script component as

public override void Input0_ProcessInputRow(Input0Buffer Row)
         if (Row.PersonSID == -1 || Row.LocationSID == -1)
         {// Redirect all Rows with unknown values                
             Row.ErrorMessage = "BusinessRule Check: Unknown Person or Location";
           // Direct all Rows including the unknown values
           if (Row.PersonSID == -1 || Row.LocationSID == -1)
               // Message to Unknown Records while directing all Rows
               Row.Message = "BusinessRule Check: Unknown Person or Location";
             {  // Message to Valid Records in direct all Rows
               Row.Message = null;


Points to consider:

1. SSIS is a powerful tool to implement one functionality in different ways and this is one of the alternate option using script component
2. Single script component will replace any number of business validation checks in the same scenario i.e. imagine a package with 10 business rules requires more than 35 components and will replace all with one script component
3. The choice of implementation depends on design decisions besides maintainability and portability.

Categories: SSIS Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: