Conditional Split Transformation

The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.

You can configure the Conditional Split transformation in the following ways:

  • Provide an expression that evaluates to a Boolean for each condition you want the transformation to test.

  • Specify the order in which the conditions are evaluated. Order is significant, because a row is sent to the output corresponding to the first condition that evaluates to true.

  • Specify the default output for the transformation. The transformation requires that a default output be specified.

Each input row can be sent to only one output, that being the output for the first condition that evaluates to true. For example, the following conditions direct any rows in the FirstName column that begin with the letter A to one output, rows that begin with the letter B to a different output, and all other rows to the default output.

Output 1

   SUBSTRING(FirstName,1,1) == "A"

Output 2

   SUBSTRING(FirstName,1,1) == "B"

Integration Services includes functions and operators that you can use to create the expressions that evaluate input data and direct output data. For more information, see Integration Services Expression Reference.

The Conditional Split transformation includes the FriendlyExpression custom property. This property can be updated by a property expression when the package is loaded. For more information, see Using Property Expressions in Packages and Transformation Custom Properties.

This transformation has one input, one or more outputs, and one error output.

Configuring the Conditional Split Transformation

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the Conditional Split Transformation Editor dialog box, see Conditional Split Transformation Editor.

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

For more information about how to set properties, click one of the following topics:

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.