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

 

 

My Z80 Homebrew Computer - The Pony80

 

 

 

 

13 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!

Anonymous said...

Thank you its helpfull

Ali Farooqi said...

Thanks a lot !

an SSIS amateur said...

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

Unknown said...

supper helpful

Nilson Nescáo said...

Thanks, man! It helped me a lot!

Anonymous said...

Thanks a lot !

F**k SSIS !

Anonymous said...

Thank you!!!!!!!

Anonymous said...

Thank you, still useful after so many years!.
Btw, the following also works (but not as clean!):
[CustNum]=="" ? NULL(DT_WSTR, 4) : [CustNum]