SSIS - Redirect bad data by Conditional Split Transformation

In this tutorial, we are going to learn how does Conditional Split transformation work in SSIS package. The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. 

We can use conditional split between our source flat file and the destination SQL Server table to redirect bad data from flat file source. If there's a bad record and we know that which column can have the bad data then based of the business rules, we can put some condition and going to write is bad, go to the new SQL dump table, otherwise, go to the "good" table.

As we already stated that the transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.

Note - The Conditional Split transformation directs each input row to one output only. If we enter multiple conditions, the transformation sends each row to the first output for which the condition is true and disregards subsequent conditions for that row. If we need to evaluate several conditions successively, we may need to concatenate multiple Conditional Split transformations in the data flow.

Based on the functionality of  Conditional Split transformation, it ensures us that every every row has only one output. To understand the bad data handling, we can assume that we have a sales data where some rows have missing order date or some rows have missing ship date missing, then we can redirect these rows by using the following condition -

[Order Date] == "" || [Ship Date] == ""

To learn more on bad data handling, please visit my YouTube channel -