Dinesh Asanka
Execution of SSIS Package with SSIS Conditional Split transaformation.

SSIS Conditional Split overview

September 4, 2019 by

SQL Server Integration Services or SSIS is used as an ETL tool to extract-transform-load data from heterogeneous data sources to different databases. After extracting data from the different sources, most often there are a lot of transformations needed. One of the frequent transformations is SSIS Conditional Split.

Scenario

Let us assume we have a set of employees, who has different payment types such as Permanent, Temporary, and Commission bases. As you know, different payment types need different calculations. Let us assume following is the data set.

Sample data set to demonstrate SSIS Conditional Split

Now the requirement is to perform the calculation for different payment types. This means that you need to split this data set into different payment types and they perform the relent calculation.

SSIS Implementation

Let us implement this in SSIS.

First, create an SSIS project in Visual Studio and open the existing DTSX package.

Since this is a data flow task drag and drop Data Flow Task to the control flow as shown in the below image.

Data Flow task in the Control Flow.

Then double-click the Data Flow Task which will open in the data flow pane.

Since we are extracting data from a text a file, let us create a connection to the text file and create a source for it from the Flat file Source.

Setting up flat file connection

Since this text file is a comma-separated value, the Delimited format is selected which is the default setting in the flat file connection along with the other default settings.

Following is the sample data set which can be seen after setting up the flat file connection.

Data set view in the Flat Fiel connection Manager

Now we are ready to separate incoming data set to different payment types.

SSIS Conditional Split Control

SSIS Conditional Split control can be seen in the SSIS toolbox under the Common category as shown in the below image.

SSIS Conditional Split in the SSIS Tool box.

Drag and drop the SSIS Conditional Split control to the data flow and connect with the flat file connection as shown below.

Connecting SSIS Conditioal Split control to the Data Source.

As shown in the above image, SSIS Conditional Split control is renamed to Split for Different Pay Types for better readability.

Next is to configure the SSIS Conditional Split Control which can be done by double-clicking the Conditional Split Control.

Configuration of SSIS Conditional Split Transformation.

Above are most of the important configurations in the SSIS Conditional Split Control. In this configuration page, you need to provide the condition which will be used to split the data set. Scripting in the conditional split configuration needs the VBScript format.

In the above configuration, three conditions are configured. In this configuration, the dataset is divided into three conditions. Developers have the option of drag and drop the column names from the above to the condition which will become much easier for the developers. These conditions can incorporate with inbuilt string functions, mathematical functions, Date/Time functions, NULL functions.

Though the above configurations are fairly straightforward, there can be instances where the split condition can be complexed. When there is a complex condition, there can be instances where one record may fall into multiple conditions. Due to the Priority order, when a record satisfies a condition, it will not be evaluated again.

In the above configuration, there is a Default output name called Other. This is to transfer all records which do not fall into any of the above conditions. This means that all the records coming into the SSIS Conditional Split control will be output from the control.

Next is to configure the output from the SSIS Conditional Split.

Output Paths for SSIS Conditional Split Tranformation.

As you can see in the above screenshot, there are four paths coming out from the SSIS Conditional Split control.

Configuration of SSIS Conditional Split control outputs.

As seen from the above screenshot, the output is split into four paths and after this, each path is independent of the other path. This means that different transformations can be done for different paths as seen in the following image after executing the package.

Execution of SSIS Package with SSIS Conditional Split transaformation.

As shown in the above screenshot, all eight records are split into four paths. Relevant records can be viewed by enabling data viewer at the relevant path.

Best Practices

Most of the time, developers forget to configure the Other path as shown in the above example. Most of the time, developers will configure the split conditions for their requirement. However, with overtime, when there is a new configuration comes to the data source, this will not be considered. The better option would be, at least move the default path to the audit log so that it can be viewed different times to identify whether those records need to be considered so that conditions can be modified accordingly.

Conclusion

Always remember to configure the error output. This applies to every transformation in SSIS. Since you are dealing with data which you don’t have control over, you don’t know what are the data coming in. Therefore, it is always better to configure the error and redirect to a different target.

Dinesh Asanka

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka
Dinesh Asanka

Latest posts by Dinesh Asanka (see all)

131 Views