Home > SSIS, SSIS programming > Programming Conditional Split Transformation

Programming Conditional Split Transformation


There may be some circumstances in a sanctuary where it becomes necessary to keep a leash of foxes and a pack of wolves separate so that they must not fight with each other and make things a mess. Similarly, in the database jungle we do the same things by using some conditional statements in order to keep things distinct. SSIS provides a nice transformation to do the same, i.e. Conditional Split Transformation.

By using this conditional split transformation, the incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions.



For Example:

Let us have an employee table in our OLTP database which contains information about employees along with the departmental information. Our requirement is that we need to split the data of employees into our OLAP database to two separate tables; one containing information about sales employees and second containing all other employees’ information.

The table structure of the employee table in OLTP database is:

Column_name

Type

employee_Sl_No

int

Department

varchar

emp_name

varchar

emp_id

int

emp_DOB

datetime

The employee table contains the below data:

employee_Sl_No

Department

emp_name

emp_id

emp_DOB

1

sales

Arun

101

1984-03-16 00:00:00.000

2

sales

Akash

102

1982-01-31 00:00:00.000

3

marketing

Ram

103

1986-03-03 00:00:00.000

4

marketing

Rasi

104

1986-04-25 00:00:00.000

5

manufacture

viswa

106

1982-04-22 00:00:00.000

6

finance

Kishore

107

1980-08-26 00:00:00.000

The logical diagram of the above requirement is:

conditionalsplit1

The dataflow component to achieve the above logic with conditional split transformation is:

conditionalsplit2

The condition for the above logic is given below:

conditionalsplit3

Note: For detail about the other components of the package, please refer to my earlier post named “SSIS Programming Basic

After successful completion of the package, we will have 2 rows in salesEmployeeDim table and the remaining 4 in OthersEmployeeDim.

conditionalsplit4

This can be done very easily by Integration programming. For this we have to create a package first, and then have to add the tasks and the precedence constraints accordingly. Then we have to add the package variable. In the dataflow task, we have to add the data flow source, the conditional split transformation and two data flow destination components.

The creation name for conditional split transformation is “DTSTransform.ConditionalSplit.1” and the component class ID is “{53A228EE-EBFA-48D6-A1AC-5269E5824A2C}”.


The code below shows the above package programmatically and the code itself is self-descriptive. The code for SQL server 2005 and SQL Server 2008 are a bit different; both the approaches are given below.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

Expand image C# .Net Expand
Expand image VB .Net Expand

Programming Integration Service in SQL Server 2008

Expand image C# .Net Expand
Expand image VB .Net Expand

Like the above we can create any number of outputs with an expression that evaluates to a Boolean for each condition.

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download
the free version from here.

Reference:
http://msdn.microsoft.com/en-us/library/ms137886.aspx

Categories: SSIS, SSIS programming
  1. Jorge Novo
    December 22nd, 2011 at 22:36 | #1

    Thanks for your examples and code, you help me understand the proper way of doing the Conditional Split.

  2. Assaf Shalem
    February 8th, 2013 at 22:50 | #2

    Many Thanks, great articles!!!!!!

  1. No trackbacks yet.
You must be logged in to post a comment.