Conditional Split Transformation in SSIS

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.