SSIS - Derived Column Replace NULL Values with empty string

Question

ISNULL([1 (Q1_1) ])? "": [1 (Q1_1) ]

When I drag the column under the Columns box in the top left, that's how my column appears,

[1 (Q1_1) ].

Why is my expression not working? I want to replace NULL values with an empty string.

Answer 1

By default, if you just add the above expression into a Derived Column Expression, SSIS will give the column a name like Derived Column 1 For debugging purposes, I prefer this although I tend to rename the column into something so I know this value was computed from the data flow task.

Set up

I have an OLE DB Source -> Derived Column -> Derived Column. The second derived column is simply an anchor point for a data viewer

OLE DB Source

I use a query as my source to test your expression. The actual source does not matter here but I find a direct query is the easiest for people to reproduce in their environment.

SELECT
    D.col as [1 (Q1_1) ]
FROM
(
    VALUES ('ABC')
    ,(NULL)
   ,('Pickles')
   ,('More pickles')
   ,('NULL')
)D(col);

I have 5 values, 4 strings and one instance of NULL. The final null is simply the word representation of the letter N-U-L-L and not the NULL value. I've had debugging sessions where the literal text was at play and not the null placeholder.

Derived Column

• Derived Column Name: Derived Column 1
• Derived Column:
• Expression: ISNULL([1 (Q1_1) ]) ? "" : [1 (Q1_1) ]
• Data Type: Unicode string [DT_WSTR]
• Length: 12

Derived Column 1

Nothing set in here but this allows me to put a Data Viewer between the two components and when I run, this is what I see

As expected, the resulting value in the second column for the second row is an empty. The second column for the other 4 rows are a repeat of the value in the first column.

Now that I know that works as expected, if I toggle the <add as new column> to Replace '1 (Q1_1) ' and re-run, I should see the same expected values, this time in the "original" column