sqlsql-server-2008t-sqlssisdata-warehouse

SQL SSIS using Derived Column Transform to deal with null data.. alternatives?


I have a source table that has 200+ columns, I am putting this into SSIS and using "OLEDB Source" component to feed the data in then mapping the flow to a Derived Column Transformation to deal with null numeric/date/text data by replacing it with blank values.

Currently, I am setting in my Derived Column the following expression:

ISNULL([EMPLOYEE ID]) ? "" : [EMPLOYEE ID]

ISNULL([EMPLOYEE FNAME]) ? "" : [EMPLOYEE FNAME]

etc...

Since I have 200+ columns, I would have to do this 200 times in the Derived Transform, is there a better way of handling this using SSIS?

Running SQL Server 2008 Standard on Windows 2008R2.


Solution

  • I ended up sticking with SSIS's Derived Column transformation and inserting each column and checking ISNULL([EMPLOYEE FNAME]) ? "" : [EMPLOYEE FNAME] as this is the method I knew best.