SSIS Conditional Split Transformation

Like Multicast transform, the Conditional Split transformation also can be defined with multiple outputs. But instead of copying the input data as an output, it evaluates the defined expressions and based on the result to direct output. It mostly likes the Switch Statement in C#. At run-time, each row in the data flow will be evaluated the condition of the first output, if it is true, the row will go to the output otherwise start to evaluate the next condition of the second output. If the row cannot meet any conditions we defined, it will go to the default output. A default output must be defined for each Conditional Split transformation.

Note Note
Each input row can only be sent to one output.

Let's do some testing on this transform.

  1. Make a copy of Multicast.dtsx in the project LearnSSIS2 and rename the copy package to ConditionalSplit.dtsx.

  2. Open the package and click Data Flow tab at the top to go to design panel. Then remove the Multicast transform.

  3. Drag & drop the Conditional Split transform to the panel and link them as shown below.
  4. [Add Conditional Split]

  5. Right-click Conditional Split transformation and choose "Edit..." to open its editor and make the settings as follows.
  6. [Conditional Split Editor]

    The default output is defined as "Default" and you can configure the error output on this transform as well.

  7. Connect the output "Price < 60" to OLE DB Destination. A dialogue box will be popped up as below. You then choose "Price < 60" and click OK button.
  8. [Input Output Selection]

  9. Repeat step 5 to connect the output "Price < 100" to the Row Count transform.

  10. Enable the data viewer on these 2 outputs. Then run the package.
  11. [Output Data In Data Viewer]

    The result is the same as that we expected. Only one record whose price is greater than 100 is going to Default output which is not defined to accept the data in the data flow.