Conditional Split Transformation in SSIS

The Conditional Split transformation moves data rows to different outputs depending on the content of the data.

The implementation of the Conditional Split transformation is similar to a CASE statement. 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.

Lets see the implementation of Conditional Split Transformation in SSIS which will check the marital status of customer and based on that insert the Married, Single, or unspecified (blank) customer’s data into separate destination excel sheets.

Here is our source data table DimCustomerDetails, and you can see the number of customers who are married, single, and not specified (blank).

Lets create a new SSIS package in existing SSIS project as shown below, or you can create new SSIS project.

Right click on SSIS Packages folder then select New SSIS package.

After that, you will see a package named package1.dtsx is created. Right click on Package and give it an appropriate name as ConditionalSplitTransformation.dtsx.

Lets go to ToolBox pane select Data Flow Task and drag into Control Flow.

Now double click on Data Flow Task, it navigates you to Data Flow view.

Next, go to Toolbox, under Other Sources select OLEDB Source component and drag into Data Flow.

After that double click on OLEDB Source component to configure the connection manager for OLEDB Source.

You will see a OLEDB Source Editor window opens, here you can select an existing connection manager, if you have already created or can create a new connection manager.

Lets click on on New.. button to create a new connection manager.

Once you click on New.. button, a Configure OLEDB Connection Manager window opens, just click on New.. button.

After that a Connection Manager window opens, just select your SQL Server Instance name, Authentication mode, and database name.

Also you can verify connection by clicking on Test Connection button.

Once you done with this just click on OK button.

After that you can see, a connection manager is created, just click on OK button.

Now you can see, the connection manager you created is populating in OLEDB connection manager dropdown box.

In Data access mode, leave it as default selected value as Table or view.

Next, select table name, also can see the data preview by clicking on preview.. button.

Now in Columns tab, you can rename output columns and remove any column by unchecking them.

After that in Error Output Tab, you can specified the behavior of the component in case of failure, you can specified any one option as follows:

- Ignore Failure: It ignores any failure while reading rows from source and the package will continue executing even any error occurred.
- Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
- Fail component: It stops the execution of package in case of any failure.

We go with default selected value that is Fail component.

After that click on OK button.

Now we will split the OLEDB Source output based on condition and load them into specified destination, Conditional Split Transformation task checks for the specified condition. It moves the data to an appropriate destination depending upon the condition.

Now go to Toolbox, under Common task, select Conditional Split Transformation component and drag into Data Flow view as shown below.

Now connect the path, drag OLEDB Source output arrow with Conditional Split transformation as shown below.

Now double click on Conditional Split, to specify the conditions.

You can see it has following sections as given below.

Variables, parameters & columns : you can use system variables and source output columns in the expressions to split the data flow.
In-built functions : you can use the various in-built functions, operators, type casts such as Mathematical, String, Date/Time function, Logical function, Null function, Operators and type Casts.
This section allows you to specify the conditions to split the data and based on that it moves data rows to different outputs depending on the content of the data.

Now expand, Columns folder then select MaritalStatus and drag into bottom section as shown below.

Lets specify the Conditions as given below.

MaritalStatus ="M"

Also rename the output column name as Split M, after that click on OK button.

After that, we will specify one more condition for Marital Status is Single.

MaritalStatus ="S"

Then give the column name as Split S.

Now to store the output based on split condition into destination, lets take Excel Destination.

In toolbox, under Other Destinations select Excel Destination and drag into Data Flow view.

Now drag Conditional Split arrow and connect it with Excel Destination, once you do this, you will see an immediately a Input Output Selection pop up window opens.

Select Split M from dropdown then click on OK button.

Now double click on Excel Destination component to configure a connection manager.

Once you double click on Excel Destination, a Excel Destination Editor window opens.

Now specify the excel location, to load the data from source. If you have an empty excel file just select that file path, else define a file name in File name textbox and click on Open. It will create a excel file on that specified location then load data.

Lets specify the file name SplitcustomerData then click on open button.

After that, click on OK button.

After that, click on New button, to create a new table, or you can say an excel sheet to load the data.

You can see it generates a table scripts based on output columns of source.

Lets rename the table as Married, then click on OK button.

Now select the table, that is Married.

In Mapping tab, you can map available source input columns with destination columns, or rename columns.

In Error Output tab, you can specified the behavior of the component in case of failure.

Here we keep selected default value Fail component.

After that click on OK button.

After that, drag one more Excel Destination component, and connect it with Conditional Split.

Now select Split M output from output dropdown, then click on OK button as shown below.

Now double click on Excel Destination 1, and this time we will use an existing connection manager that we have just created earlier for Excel Destination component.

But to insert the data for customers who have marital status “Single”, we will take a separate sheet in excel, so just click on New button, you will see it generates a table scripts just rename the table as Single, then click on OK button.

Next, you can go in Mapping tabs, to map input columns with destination columns, or in case rename the output column.

After that you can also go to Error Output tab to specify the behavior of the component in case of failure.

Once you done with this, just click on OK button.

So far, we have configured OLEDB source, specified Conditional split transformation, and connect it with destination excel. So that married customer’s data will be inserted into excel sheet named married, and Single customer’s data will be inserted into same excel but separate sheet named Single.

Now drag one more Excel Destination, then drag Conditional Split arrow and connect with Excel Destination 2.

As Split M, and Split S has already been assigned, you will see SSIS by default assign output Conditional Split Default output to this, so that if a row matches no condition it is directed to the default output.

Now double click on Excel Destination 2, to assign a connection manager for this.

Again, we will use an existing excel connection manage, but will create a new table.

So just click on New.. button, and you will see it generates a table scripts just rename the table as Unspecified.

After that click on OK button.

Now our package is ready, and it will look like as shown below.

Lets execute the package, right click on Data Flow view then select Execute Task.

You can see, package run successfully. You can see there are 18484 rows, out of that 8994 are Married customers, 7641 are Single and remain 1849 rows are Unspecified.

Lets check the excel sheet, and you can see there are three sheets, one includes Married customer’s data, second includes Single customer’s data, and third sheet includes Unspecified (blank)customer’s data.

First sheet includes, Married customer’s data.