Return a NULL DT_STR in a conditional statement

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?



26 Responses to Return a NULL DT_STR in a conditional statement

  1. Jamez says:

    Thanks! this saved me. I could never figure out why conditional statements would not work, always getting “NULL” is not an input column.

  2. Joseph says:

    Thank you; this also saved me. I think this is poor SSIS design…

  3. Matrix says:

    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.

  4. Jon G says:

    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.

  5. […] to this blog entry for pointing me in the right […]

  6. Alex O says:

    THANK YOU!!!!

  7. Peter Ng says:

    Thanks a million!

  8. 🙂 Spent an hour on this, but luckily you had blogged about it, and I found it using Google. On to the next error!

  9. Satosh says:

    You made my day

  10. Sunel says:

    Thank You.

  11. Russ says:

    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!

  12. mioux says:

    Wow, this is the solution 😀

    Casting a NULL value… what was in your mind when you found this solution ? 😀

  13. luke says:

    Thanks for this.
    The fact that SSIS needs you to do this to avoid an error is stunning.

  14. Naveen says:

    Hey thanks for the post. This really saved me lot of time.

  15. Abraham says:

    Great!!!!!! That is perfect

  16. Dietrich says:

    Many thanks! What crappy design! I’m gonna get some sleep now 🙂

  17. Mehul says:

    Thank you Friend !
    You have saved m day. Cheers.

  18. Kate says:

    Thanks! Yet another person you have saved a lot of time for.

  19. William Tang says:

    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.

  20. Mark says:

    Thanks a lot, you saved me an headache 🙂

  21. dbalevi says:

    Just wanted to echo everyone else and say thanks. This was annoying the hell out of me!

  22. kris says:

    excellente! saved my day…found this and solved my problem in 10 mins! Thanks for sharing!

  23. Jeff W. says:

    More than 5 years on, and this post is still useful. Great tip!

  24. Raul says:

    Isn’t this solution replacing empty string with a string ‘NULL’ instead of actual null value. Or it’s just me

  25. KOKO says:

    So annoying. I agree, it is redundant to cast the null type when the null type was already specified.

  26. Christian Loris says:

    Thanks…. saved me a headache… love SSIS and it’s quirks.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: