Conditional Split - SSIS Data Flow

The Conditional Split transformation in SSIS allows you to route data rows based on specified conditions. It is used in the Data Flow task to split data into multiple output paths. This transformation is useful when you want to filter, sort or group data based on certain conditions.

Syntax

• Right-click on the Data Flow task and select “Edit” to open the Data Flow tab.
• Select the Conditional Split transformation from the Toolbox.
• Drag the Conditional Split transformation onto the Data Flow.
• Connect the data source to the Conditional Split transformation.
• Double-click the Conditional Split transformation to open its editor.
• Under “Condition,” add one or more logical expressions that define the conditions for splitting the data.
• Under “Output,” define the output paths and their corresponding conditions.

Example

Suppose you have a dataset that contains information about a company’s sales in different countries. You want to split the data into two output paths: America and Non-America, based on whether a given row’s “Country” column contains “America” or not.

Here’s an example of how you might set this up:

• Condition 1: Country == "America" => Output Name: America
• Condition 2: Country != "America" => Output Name: Non America

Output

Based on the above example, the data will be split into two output paths - America and Non America. The rows of data that contain Country as America will be sent to America output and the rest of the rows will be sent to Non America output.

Explanation

The Conditional Split transformation takes one input and produces one or more outputs based on one or more conditions defined in the transformation editor. You can define multiple ouputs and these outputs can be individually mapped to different destinations. You can specify multiple conditions and they will be evaluated in the order in which they appear.

The transformation editor allows you to specify your conditions using a graphical user interface, which makes it easy to create and modify complex conditions.

Use

The primary use of the Conditional Split transformation is to filter and group data based on certain conditions. You can use it to separate data based on certain criteria like date ranges, region, customer types, etc.

Important Points

• The Conditional Split transformation cannot modify the data being processed, it can only route it.
• The order of evaluation of conditions is important and will impact the output of the transformation.
• Each condition must evaluate to either true or false.

Summary

The Conditional Split transformation is a useful tool in the SSIS toolbox that allows you to split data based on one or more conditions. It is a powerful way to separate and transform data as it flows through an ETL process and can be used to filter datasets based on complex criteria.