Conditional Split Task in SSIS

Introduction

In this article we are going to see on how to use a Conditional Split task based on the decision. Conditional tasks transformation will be used to split the data based on some condition and save the result set in different destinations. This task will be very useful at cases like to insert some passed test cases data to Database and the failed test cases data to the text file or sending a mail to the administrator.

To follow my series of articles on SSIS packages refer to the below URL’s

http://www.dotnetfunda.com/misc/page32.SQL-Server-Integration-Services-Tutorials-SSIS.aspx

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use Conditional Split transformation task options available with SSIS.

Once the project is opened, now we can proceed to the steps on how to create a conditional split and see how to configure the task.

Drag and drop a Data Flow Task and double click on the same will open the Data Flow tab as shown in the below figure add a Flat File source which is going to be an input for the package. I have configure the Flat File source (Refer to previous articles on how to configure the Flat File Source).

Input file for the Flat File source will be like below

Here we are going to save the application name into different destination files based on the Country (US or India). To achieve this task I just dragged and dropped the Conditional Split task as shown in the above image. Now in order to configure the task just double click on the Conditional Split task

To make this configuration as shown in the above image just drag and drop the Column which u are going to make it as a condition here in this example I dragged and dropped Column 4 to the bottom pane and give the condition(both) as shown in the above screen.

Once above configuration is done click on the OK button. Now we need to make the destination configure. Here I’m saving the destination data to a different Flat File as FILEUS and FILEINDIA. So drag and drop 2 flat file destinations and configure as shown in the below screen

Once configured now click on the F5 button to build and execute the package. You will see the below screen once the execution is completed.

You can see 2 new files created for US and INDIA countries as shown in the below screen.

Conclusion

In this article we have seen on using the Conditional Split task on how to configure and use the same in order to achieve multiple destination output based on the condition.