How to filter null value rows by using Conditional Split Transformation in SSIS

Scenario:

Sometime we have Null values coming from Source. In this post we will learn how to redirect those records by using Conditional Split Transformation.

For this post, I have created a sample data file by using below data.

ID,Name1,Aamir2,Raza,, , 3,John4,Sam,Rebecca

Step 1:

Create an SSIS Package and bring the Data Flow Task to Control Flow Pane.Inside Data Flow Task bring the Flat File Source. Create the connection to the text file.

Fig 1: Create connection to text file by using Flat File Source

Check the property as shown below to convert the blank into Null values in Flat File Source.

Fig 2: Convert the blank values to Null values in Flat File Source

Step 2:
Bring the Conditional Split Transformation to the Data Flow Pane and connect Flat File Source to it. Once connected, configure the Conditional Split Transformation. In this blog we want to redirect any records which has Null value for ID column. The ISNULL() function can be used to find Null value rows.

Step 3:
Connect two Multicast Transformation to the Conditional Split Transformation, One Output NullRecords will be connect to one Multicast and default Output to second Multicast Transformation.
Add the Data Viewer between Conditional Split Transformation and Multicast Transformations to see the output.