Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Conditional Split Transformation in SSIS

, 5 Aug 2014
Rate this:
Please Sign up or sign in to vote.
Conditional split is used to route data rows to different outputs based on conditions. This is similar to CASE statement in programming languages. Here I am explaining conditional split with an example.

Overview

Conditional split is used to route data rows to different outputs based on conditions. This is similar to CASE statement in programming languages.

Here I am explaining conditional split with an example.

Requirement is to route country specific data from a SQL Server tables to different flat files.

Step 1

Drag and drop a data flow task to the package under Control Flow.

Step 2

Double click on the dataflow task and define the flow of data.

Step 2.1

Create source DB connection and destination flat file connections under connection managers. Drag and drop and OLE DB Source to select the source table data. Double click on the source and I have selected the data  using Sql Command data access mode.

Step 2.2(Optional)

Transformations will vary based on the data to be populated to the destination. Here I have added a Derived Column  to handle null values in the middle Name.

Step 2.3

Drag and drop a Conditional Split tool from the tool box

Double click on the Conditional Split , transformation editor pop up will be  shown. Here we can define the different conditions and routing will be done based on the condition we define here.

So there are 3 routes defined in the above screen each condition will redirect the rows to 3 different routes and rest of the rows will be routed to the fourth route.

Step 2.4

Create flat file destinations for each routes.

For each destination, select corresponding connection manager that we have already created.

Step 2.5

There will be four outputs from the Conditional Split. Drag and drop each outputs in to correspond destinations. There will be option to choose the out put.

Finally the Data Flow  will look like this:

Step 3

Execute the package and the files will be created in the file location created in the locations configured under the connection managers.

Conclusion

Hope this will help you to implement conditional statement logic in your SSIS Packages.

Thank you for reading the article.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sreekanth Mohan
Software Developer (Senior)
India India
I am a software Engineer specialized in Microsoft technologies(Asp.Net, SQL Server, MSBI etc).

Comments and Discussions

 
QuestionImages are offsite. PinprofessionalSoMad17-Aug-14 12:59 
QuestionThis was a tip... PinprotectorOriginalGriff16-Aug-14 22:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 5 Aug 2014
Article Copyright 2014 by Sreekanth Mohan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid