How do I change NULL values to empty strings?

Question

I have an SSIS Package that is copying data from a column that is Nullable to a table where the same column is not Nullable. There is red tape involved in making the source not nullable so for now I need a way to change the nulls to empty strings.

I get the data from an ADO .Net Source, not a query where I could just add a check for null. If need be I can switch to a query and just do the check at that point. Before I do that I wanted to see if there is an SSIS tranformation that would allow me to switch the Null to empty string (and still use the same column).

Answer 1

Something similar to this might do the trick for you. It's actually an example of doing the reverse operation (from empty string to NULL) but at least shows the conditional operator in use.

Return a NULL DT_STR in a conditional statement

Answer 2

There is a new function REPLACENULL in SSIS 2012 that can be used.

REPLACENULL( [your_column_name], "" ) 

If SSIS 2008 is used, ISNULL(...) ? ... : ... from previous answers is the best solution.

Answer 3

Use the Derived Column transformation. You would then select replace your_column_name from the derived column drop down and then populate the expression property with this code:

ISNULL( [your_column_name]  )  ? " " : [your_column_name] 

Hope this helps.