CODEWITHSUNDEEP
ssis
Vaccano
Vaccano

Reputation: 82517

How do I change NULL values to empty strings?

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).

Upvotes: 6

Views: 25644

Answers (3)

Ilya Palkin
Ilya Palkin

Reputation: 15797

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.

Upvotes: 3

davek
davek

Reputation: 22925

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

Upvotes: 3

rfonn
rfonn

Reputation: 2221

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.

Upvotes: 12

Related Questions