Conditional Split in SSIS

Madhu.b.rokkam
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 29680 red flag

This article desccribes conditional split in SSIS packages.

Introduction

At times we come across situations where we had to split data based on some conditions. And doing this is very easy in SSIS Conditional Split task.

The Conditional Split can be used to transfer data to different outputs depending on the condition specified and content of the data. This almost acts the same way as we have Switch Case statement in programming language. This too has the default option to redirect rows if non of the conditions met.

Let us consider a scenario where we have a file containing all the students info in a file and we need to separate all male students into one file and all female students into another.

So lets Start..

1. Start SQL Server BI development project, Select Integration Services Project template. On the package1.dtsx file add a data flow task as shown.

2. Double click the data flow task. Add a Flat file source and configure it to point to source file. Check the columns tab.

3. Add a conditional split and double click on that to configure as below



4. Now add two Flat File Destination components and attach the conditional task to them using the green arrow line. An input output selection pop up comes up where you need to select Males and click ok.

Similarly connect another green arrow to other Flat file destination and in the input output selection box, select Females.

5. Configure the Flat file destination for Males data first. Double click on the component, Click on new, Select the format as delimited, Click Ok. Set the output file path and then click on Mapping tab and finally click ok.

6. Similarly configure the Destination output file for the females info.

7 The final package would look like this.


 

8. Finally lets create some sample data file

9. Execute the package and you will see that there are two files created in the output folder.

Thats it..

Conclusion

Hope you all would like this. Feel free to share your comments or feedback. Also any other samples you need like from me to create using SSIS packages.

Page copy protected against web site content infringement by Copyscape

About the Author

Madhu.b.rokkam
Full Name: Madhu Rokkam
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/13/2011 3:13:20 PM
Country: India
Thanks and Regards Madhu
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)