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

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

Answer 2

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.

---

UPDATE: As suggested below, the IIF should be removed.

ISNULL(column)?"":column

Answer 3

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 4

In your query you can use like this

CASE Tablename.ColumnName WHEN NULL THEN ' ' ELSE Tablename.ColumnName END AS 'Column Name'