: Setting a NULL value in SSIS derived column

Wednesday, July 14, 2010

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

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.; July 12, 2013 at 3:45 PM
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.; December 5, 2013 at 10:53 AM
Anonymous said...: Thank you for this!; March 18, 2014 at 12:21 PM
Mark Iannucci said...: Thanks!; April 17, 2014 at 4:54 PM
Arif Baig said...: Thank you! You saved me from tearing off the rest of my hair.
Peace!; April 17, 2014 at 5:00 PM
Anonymous said...: Thank you its helpfull; August 26, 2014 at 8:14 AM
Ali Farooqi said...: Thanks a lot !; October 8, 2014 at 3:02 PM
an SSIS amateur said...: THANK YOU. thank you. wow thanks. super thank. seriously many gratitude; June 22, 2015 at 12:04 PM
Unknown said...: supper helpful; November 4, 2015 at 10:23 AM
Nilson Nescáo said...: Thanks, man! It helped me a lot!; July 12, 2016 at 10:41 AM
Anonymous said...: Thanks a lot !

F**k SSIS !; June 5, 2018 at 6:04 AM
Anonymous said...: Thank you!!!!!!!; June 27, 2019 at 11:25 AM
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]; January 10, 2021 at 7:13 PM