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:

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

The condition for the above logic is given below:

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.

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}”.