Posts Tagged ‘ScriptComponent’

SSIS – Alternate implementation of Business rule validation using Script Component

October 22, 2012 Leave a comment

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: