Apr 21, 2012

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


No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.