SSIS Conditional Split Transformation

SSIS Conditional Split Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

The Conditional Split transformation is a fairly simple one: based on the condition set defined in it, it will redirect every row to one of its outputs. This transformation can have only one input, and it might have multiple outputs. The transformation always has a default output, which is similar to the "otherwise" case in some programming languages: if no condition can be evaluated as true for a record, then that record will be redirected to this output.

 

 

The conditions are defined with the syntax of SSIS Expressions, and the condition set is evaluated in the order of the definition. If a condition is true for a record, then the record is redirected to the corresponding output, and no other conditions will be evaluated. In the example above, all orders after January 2012 will be redirected to the default output.

 

 

This is a partially blocking transformation - however every output row has a 1:1 relationship with an input row, outputs of the transformation are copied into new buffers. The outputs are not necessarily connected to another (transformation or destination) component - in this case no records have to be handled on that data flow path. This is how filtering records is possible with SSIS: the outputs of the unnecessary records are not connected to any other component.

So, the Conditional Split transformation can be used to split the a data flow path to multiple paths, or filtering the data in the middle of a data flow. It sends each row to one of the outputs, in contrast to the Multicast transformation, which multiplies data by sending all rows to all of the outputs.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Zoltán Horváth edited Original. Comment: added MSDN reference

  • Zoltán Horváth edited Revision 1. Comment: clarifying buffer usage, comparing to Multicast, linking Multicast

  • Zoltán Horváth edited Revision 6. Comment: Correcting Red Link

  • Hi Zoltán, the Conditional Split and Multicast don't create new buffers. Wouldn't that make them non-blocking?

Page 1 of 1 (4 items)