Split a Dataset by Using the Conditional Split Transformation
To add and configure a Conditional Split transformation, the package must already include at least one Data Flow task and a source.
To conditionally split a dataset
In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
Click the Data Flow tab, and, from the Toolbox, drag the Conditional Split transformation to the design surface.
Connect the Conditional Split transformation to the data flow by dragging the connector from the data source or the previous transformation to the Conditional Split transformation.
Double-click the Conditional Split transformation.
In the Conditional Split Transformation Editor, build the expressions to use as conditions by dragging variables, columns, functions, and operators to the Condition column in the grid. Or, you can type the expression in the Condition column.
A variable or column can be used in multiple expressions.
If the expression is not valid, the expression text is highlighted and a ToolTip on the column describes the errors.
Optionally, modify the values in the Output Name column. The default names are Case 1, Case 2, and so forth.
To modify the sequence in which the conditions are evaluated, click the up arrow or down arrow.
Place the conditions that are most likely to be encountered at the top of the list.
Optionally, modify the name of the default output for data rows that do not match any condition.
To configure an error output, click Configure Error Output. For more information, see Configure an Error Output in a Data Flow Component.
To save the updated package, click Save Selected Items on the File menu.