CODEWITHSUNDEEP
ssis-2008
Ruzel R.
Ruzel R.

Reputation: 327

SSIS: Replace Null Value to 0 in Derived Column expression

I'm trying to replace the Null Value of my excel file into 0, but got an error "Data type DT_14 and DT_WSTR are incompatible for binary operator "==". I'm using a derived column and so far here's my expression 20150921 == "" ? 0 : 20150928 I have also tried this one, ISNULL(20150921) ? 0 : 20150921 but the data being passed to the destination is 20150921 instead of its real value.

By the way, 20150921 is string [DT_STR] type.

Also, there's a scenario where my column 20150928 changes into F11, F12 and So on. Why is that?

Newbie here! Sorry.

Upvotes: 2

Views: 10787

Answers (1)

Lkor
Lkor

Reputation: 454

You are comparing number (20150921) with the string (""), that's why you got the error Data type DT_14 and DT_WSTR are incompatible for binary operator "==". Nevertheless your expression is almost correct, you just need to compare the name of column, instead of value of the column.

Example: You have an excel file with column "Column1" which contains numbers (20150921) and some empty values. SSIS treats every Excel column as a string, so you have to convert it by the expression: Column1 == "" ? 0 : (DT_I4)Column1. That means every empty value will be transformed to 0, otherwise it will be just cast to integer (four bytes integer). You can also use data type DT_I8.

EDIT: In case your column is named 20150921, try to use [20150921] instead.

Upvotes: 3

Related Questions