Blog Post

Replace Blank Values to NULL in SSIS

,

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.

blanks ssis 1

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.

blanks to null ssis 2

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;

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating