SSIS Null Value Questions

Question

I have a table with 5 string columns, all can be NULLs. After I read the data from this table, I want to convert any null values into empty strings. The reason is that I need to compare these columns with columns in another table of the same schema (using conditional split), and null values would cause the comparison to evaluate to NULL.

Is there any functionality in SSIS that allows me to convert NULL's to empty strings, or just not having to deal with NULL's at all?

Answer 1

You can use a Derived Column transform. I don't have VS open now, but you'd use something like:

IIF(ISNULL(column)?"":column)

as the expression, and have it replace the original column.

Answer 2

In your query wrap your columns as follows...

SELECT ISNULL(col1,'') AS [col1]
      ,ISNULL(col2,'') AS [col2]
      ,ISNULL(col3,'') AS [col3]
      ,ISNULL(col4,'') AS [col4]
      ,ISNULL(col5,'') AS [col5]

Answer 3

The correct syntax is (ISNULL(column)?"":column) without the IIF