SSIS 2008 - Conditional Split Transformation

Conditional split Transformation

As per the name this transformation splits the given dataset into different subsets based on the provided condition. This transformation has one input and provides more than one output. Supports ‘Error Routing’. 

This transformation provides default output as well which is similar to else statement in a given 'if-elseif-elseif-else' statement of programming languages. Conditions entered should evaluate to Boolean 'TRUE' or 'FALSE'. Every output can be given its own custom 'Output Name' which can help us to identify which output needs to be mapped to which flow.

The most important point to be remembered is 'order of the conditions is most important as a given row will  get routed to the output flow for which the condition evaluates TRUE and won't be evaluated against remaining conditions'.

Below example shows how to configure Conditional split transformation and the importance of having correct order for Conditions.

Sample data:

SalesRep_Id	Month	Year	Region	Country_Code	Unit_Sold
1	Jan	2011	Asia	IN	100
2	Feb	2011	EU	UK	150
3	March	2011	US	US	175
4	Jan	2012	Asia	IN	150
5	Feb	2012	EU	UK	200
6	March	2012	US	US	100
7	Jan	2013	Asia	IN	250
8	Feb	2013	EU	UK	300
9	March	2013	EU	UK	350

Conditional Split Transformation editor:

Based on the above order of conditions, the output will be as shown below:

Now if you check the sample data again, you will find that there are 6 records which have ‘sales <=200’, but in the conditions sequence, this condition is placed at the end, due to which these 6 records get routed to their respective country flow. To fix this we need to change the conditions order. To do this we can use the arrow buttons placed next to the conditions. New Corrected conditions sequence is as shown below:

Output:

To consolidate, Conditional Split transformation can be used to route the rows as well to filter out unwanted rows in the middle of the data flow.