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

Question

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.

Answer 1

In SSIS, you can use Derived column / Script transformation[as transformation] and write the code to convert the NULL with blank. There is no other alternatives in SSIS. SSIS will create the buffer to handle the calculation. It will slowdown your SSIS package. My suggestion is that try to use the ISNULL function or COALESCE function in SQL query and convert the NULL into the blank, so that all the conversion will happen in the Serverside itself.

Select ISNULL(EmployeeId,'')

or

Select COALESCE(EmployeeId,'')

Hope this helps.

Answer 2

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.