Blank values are annoying, anywhere and everywhere. If your source system contains them, here’s how to convert blank values to NULL in an SSIS data flow task.

For this demo, I created sample data at the source with blank values in “ColC” column.

You can add a Derived Column transformation and use the following expression to replace blanks to NULL.

(DT_STR,50,1252)( TRIM(ColC) == "" ? (DT_STR,50,1252)NULL (DT_STR,50,1252) : ColC )

Make sure you select “Replace Existing Column” when using this expression in Derived Column transformation. Obviously, you’ll have to replace “ColC” with an actual column name in your pipeline.

To verify, I added an Union All transformation and enabled data viewers.

As you can see above, blank values in “ColC” are converted to NULL.

Another option that works well if your source is a SQL Server database is to convert blank values to NULL in the source itself. One way to do this is to use Case in the Select statement that reads the source data. A simple example is shown below:

SELECT t.ColA ,
t.ColB ,
CASE WHEN t.ColC = '' THEN NULL
ELSE t.ColC
END AS ColC
FROM your_table AS t;