Conditional Split in SSIS

Overview

A conditional split is used to route data rows to various outputs based on conditions. This is similar to a case statement in programming languages. Here I am explaining a conditional split with an example.

The requirement is to route country-specific data from SQL Server tables to multiple 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 a source DB connection and destination flat file connections under connection managers. Drag and drop an 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)

The 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 , a transformation editor pop-up will be shown. Here we can define the various conditions and routing to be done based on the condition we define here.

 

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

Step 2.4

Create flat file destinations for each route.

 

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

 

Step 2.5

There will be four outputs from the Conditional Split. Drag and drop each output into correspond destinations. There will be an option to choose the output.

 

Finally the Data Flow will look like the following.

 

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

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

Thank you for reading the article.