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?

Mark

16 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. mike.trachta :: SSIS: Returning NULL in a conditional statement Says:

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

  6. Alex O Says:

    THANK YOU!!!!

  7. Peter Ng Says:

    Thanks a million!

  8. Jakob Breivik Grimstveit Says:

    :-) 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 :D

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

  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 :)

Leave a Reply