Conditional Split Transformation in SSIS

Conditional Split Transformation in SSIS is just like IF condition or CASE statement, checks the given condition and based on the condition, the output will be sent to the appropriate destination path. It has ONE input and MANY outputs.

For example, if we want to store the students in a class who have marks greater than 40 in one Table and the students who score less than 40 to another table then we can use this Conditional Split Transformation to split the data using the condition. Note that Conditional Split is case-sensitive.

Conditional Split Transformation in SSIS Example

STEP 1: Drag and drop the data flow task from the toolbox to control flow and name it as Conditional Split Transformation.

Double click on it and it will open the data flow tab.

STEP 2: Drag and drop OLE DB Source from toolbox to data flow region. Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement.

SQL command using for our OLE DB source is

STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.

Click ok

STEP 4: Drag and drop the Conditional Split Transformation from toolbox to data flow region and double-click on it to provide the conditions.

Conditions we used in this transformations are:

1. ROWS < 400: SalesAmount < 400
2. ROWS BETWEEN 400 TO 2000: SalesAmount >= 400 && SalesAmount < 2000
3. Remaining rows will be acted as default output and we named it as [Rows > 2000]

From the above you can observe that, We used 2 conditions and one default output in Conditional Split Transformation So, We get 3 output in total.

STEP 5:Drag and drop three OLE DB Destinations from toolbox to data flow region and rename them as Condition Split 1, Condition Split 2 and Condition Split 3

STEP 6: Drag and drop the arrow from Conditional Split Transformation to OLE DB Destination (Conditional Split 1) will pop up Input Output Selection window to select the appropriate output. Let us select the [Rows <400] output as shown below

Click ok

STEP 7: Now we have to provide OLE DB Connection Manager and table details of the Destination. So double-click on the OLE DB Destination (Conditional Split 1) and provide the required information

From the above screenshot you can observe that, We selected [Conditional Split 1] inside the SSIS Tutorials Database

STEP 8: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.

Click ok

STEP 9: Drag and drop one more arrow from Conditional Split Transformation to OLE DB Destination (Conditional Split 2) will pop up Input Output Selection window to select the output. This time we are going to select [Rows between 400 to 2000] output as shown below

STEP 10: Let us provide OLE DB Connection Manager and table details of the destination for our second output. So double-click on the OLE DB Destination (Conditional Split 2) and provide the required information.

From the above screenshot you can observe that, We selected the [Conditional Split 2] inside the SSIS Tutorials Database

Repeat STEP 8 and Click ok

STEP 11: Drag and drop one more arrow from Conditional Split Transformation to OLE DB Destination (Conditional Split 3). Here we are configuring the default output data so, Double click on the OLE DB Destination and provide the information

From the above screenshot you can observe that, We selected the [Conditional Split 3] inside the SSIS Tutorials Database

Repeat STEP 8 and Click ok

We finished developing the package. It’s time to run the package

Let’s see the result in [Conditional Split 1] Table in SQL Server. Open the SQL Server Management studio and write the below SQL select statement to check the result

Let’s see the result in [Conditional Split 2] Table in SQL Server. Open the SQL Server Management studio and write the below SQL select statement to check the result

Next, see the result in [Conditional Split 3] Table in SQL Server. Open the SQL Server Management studio and write the below SQL select statement to check the result

Thank You for Visiting Our Blog