SSIS- How To Convert Blank Into Null In SSIS

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.
Fig 1. Flat File Source with blank values 

Solution:
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)

Fig 2- Derived Column Expression to replace blank with Null values

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.

Fig 3. SSIS Data Viewer to display the data in Data Flow Task

No comments:

Post a Comment