Wednesday, July 14, 2010

Setting a NULL value in SSIS derived column

Ran into an interesting error while trying to set a null value to a column in a derived column task in SSIS.
Here is my statement:
[CustNum]=="" ? NULL(DT_STR, 4, 1252) : [CustNum]

And the error thrown:
For operands of the conditional operator, the data type DT_STR is supported only for input and cast operations.

The correct syntax for this is:

[CustNum]=="" ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : [CustNum]

Not sure why this requires the explicit cast since you include the type in the null statement.

peace

7 comments:

Anonymous said...

Thanks so much. I was having the same issue with trying to null a date true condition. I didn't run into this solution anywhere else.

Anonymous said...

Thanks a lot for this post. I was struggling with a similar issue with DT_STR datatype and your post said it all.

Anonymous said...

Thank you for this!

Mark Iannucci said...

Thanks!

Arif Baig said...

Thank you! You saved me from tearing off the rest of my hair.
Peace!

M. Ali Farooqi said...

Thanks a lot !

an SSIS amateur said...

THANK YOU. thank you. wow thanks. super thank. seriously many gratitude