How to use Conditional Split Transformation in SSIS

Description:-

Conditional Split Transformation in SSIS is just like IF condition or CASE statement, checks the given condition and based on the condition, the output will be sent to the appropriate destination path. It has ONE input and MANY outputs.

For example, if we want to store the students in a class who have marks greater than 40 in one Table and the students who score less than 40 to another table then we can use this Conditional Split Transformation to split the data using the condition. Note that Conditional Split is case-sensitive.

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. 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.

In this example we will conditionally split data and store into flat file. What we will do we will get item data from the source database and after will spilt data based on ItemId, Line Amount and store into flat file.

Now add Data flow task and configure source connection.

Now drag drop conditional split and connect input arrow to it as shown in figure.

Now choose edit option by right clicking the conditional split and configure it. When you click you will get screen like below. You will find different condition operator. One important point to remember here you need to drag drop column name to condition textbox. As our aim is to have product with product number and line amount in a different file and other product number in different file. So we use following condition as shown in below figure.

Now drag drop a flat file destination and assign the output of the above conditional split to file destination. You will get following screen. As you see we are assigning Product condition output to this file which means all the products which belongs to Product Number and Line Amount should be available in this.