Scenario:We are reading data from Flat File Source in our SQL Server Integration Services (SSIS) Package. Some of the values for Address column are coming as Blank. We want to convert those blank values to Null before loading into our SQL Server table.
We will be using Derived Column Transformation in our SSIS Package to replace these blank values with Null.
Drag Derived Column Transformation to Data Flow Pane and connect the Flat File Source to it.
Add a new column DER_Address and write expression as shown below in fig 2.
(DT_STR,50,1252)(TRIM(Address) == "" ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : Address)
Let's execute our SQL Server Integration Services Package and see if the blank values are replaced with Null. Data viewer in Data Flow Task is used to display the data for test.