SQL Server Integration Services (SSIS) - 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 in cases such as for inserting some passed test cases data to a database and the failed test cases data to a text file or sending a mail to the administrator.

Steps:

Follow steps 1 to 3 in 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 how to use the Conditional Split transformation task options available with SSIS.

Once the project is opened, 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 it; that will open the Data Flow tab as shown in the figure below; add a Flat File source which is going to be an input for the package. I have configured 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 do that 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 that you are going to make it as a condition here in this example I dragged and dropped Column 4 to the bottom pane and gave the condition(both) as shown in the above screen.

Once the above configuration is done click on the OK button. Now we need to do the destination configuration. 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 screen below.


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


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

Conclusion

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





Similar Articles