I have a requirement to convert all blank strings in a data flow into NULLs. Here is the expression I tried first:
TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]
This threw an error:
Error at Master DataFlow [Derived Column Task]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression “TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]” has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitily cast with a cast operator.
After much messing around, I foud that if I cast the NULL statement as a DT_STR, the expression compiler was happy:
TRIM( [Some Column] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [Some Column]
Is this an expected behavior of Integraion Services?
I guess I would have expected that since I specified the type in the NULL statement that I would have to cast it as a DT_STR afterwords.
Any thoughts?
Mark
Cloudy With a Chance of Data 
Thanks! this saved me. I could never figure out why conditional statements would not work, always getting “NULL” is not an input column.
Thank you; this also saved me. I think this is poor SSIS design…
Hey, it only took me about an hour of refining my search to find this golden nugget that saved me from having do use script components to do something so simple. I concur that having to cast a null that’s already been cast is redundant. If that’s the case, remove the need to specify the length and code page for the null itself.
Thanks for the tip.
I was having trouble processing a fix-width text file, always failing at Data Conversion. I narrowed it down to a Null vs. empty string problem, and after I put the condition in Derived Columns for all columns, the problem is gone.
[…] to this blog entry for pointing me in the right […]
THANK YOU!!!!
Thanks a million!
🙂 Spent an hour on this, but luckily you had blogged about it, and I found it using Google. On to the next error!
You made my day
Thank You.
This saved me too. But it is also weird since I don’t need to do the explicit type cast for other data types. I am able to use NULL(DT_I4) just fine without needing to cast it as DT_I4. For strings it seems weird and redundant, but it works, so thanks!
Wow, this is the solution 😀
Casting a NULL value… what was in your mind when you found this solution ? 😀
Thanks for this.
The fact that SSIS needs you to do this to avoid an error is stunning.
Hey thanks for the post. This really saved me lot of time.
Great!!!!!! That is perfect
Many thanks! What crappy design! I’m gonna get some sleep now 🙂
Thank you Friend !
You have saved m day. Cheers.
Thanks! Yet another person you have saved a lot of time for.
I have spent quite some time to figure out how to declare a null value in a SSIS expression. This approach made it work. Thank you.
Thanks a lot, you saved me an headache 🙂
Just wanted to echo everyone else and say thanks. This was annoying the hell out of me!
excellente! saved my day…found this and solved my problem in 10 mins! Thanks for sharing!
More than 5 years on, and this post is still useful. Great tip!
Isn’t this solution replacing empty string with a string ‘NULL’ instead of actual null value. Or it’s just me
So annoying. I agree, it is redundant to cast the null type when the null type was already specified.
Thanks…. saved me a headache… love SSIS and it’s quirks.