The Conditional Split Transformation

[James Beresford]

Fig 1: The Conditional Split Transformation


In this post I will be covering the Conditional Split Transformation. The sample package can be found*here for 2005 and guidelines on use are here.

How does data get “Split”, exactly?

The Conditional Split directs entire rows to different outputs depending on whether they meet criteria that you set. The criteria are defined using SSIS’s expression language to create a statement that evaluates to True or False. For example, Cost < 1000. You can define as many outputs as you like, and there is always one default (which you can name if you want). My example from the sample package, which directs data to four possible outputs, is below:

Fig 2: Configuring the Conditional Split Outputs


Note from the configuration example above there is an Order in which the conditions are evaluated in. As per normal programming conventions (such as a SELECT CASE or IF .. ELSE IF…) the conditions are evaluated in the order specified, so as per my example you can do a big sweep first (Gender= “F” ) before doing more detailed conditional checks on other categories. If I had a fourth condition that relied on a row having Gender = “F” it would never be met because all rows that met that criteria would have been diverted to an output by the first condition. You can use the up & down arrows to the right of the expression pane to change the order in which criteria are evaluated.

When would you use a Conditional Split?

There’s a couple of scenarios that commonly crop up for using this transformation - first is when you want to direct subsets of data to different locations, such as sending centrally collected data to appropriate branches. A second is within the dataflow when you want to apply different business rules to certain subsets of the data, before recombining it to go to the same final destination. It’s fast and once you understand the syntax for the SSIS expression language, very simple to use.

MSDN Documentation for the Conditional Split Transformation can be found here for 2008 and here for 2005.



More...