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

Possibly related posts: (automatically generated)

This entry was posted on Wednesday, August 2nd, 2006 at 9:48 am and is filed under Integration Services. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

16 Responses to “Return a NULL DT_STR in a conditional statement”

  1. Jamez Says:
    November 30, 2006 at 2:56 pm | Reply

    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:
    January 11, 2007 at 10:51 am | Reply

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

  3. Matrix Says:
    March 2, 2007 at 4:25 pm | Reply

    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:
    June 15, 2007 at 2:12 am | Reply

    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:
    October 22, 2007 at 4:24 pm | Reply

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

  6. Alex O Says:
    February 14, 2008 at 5:15 pm | Reply

    THANK YOU!!!!

  7. Peter Ng Says:
    April 2, 2008 at 1:59 pm | Reply

    Thanks a million!

  8. Jakob Breivik Grimstveit Says:
    May 21, 2008 at 7:18 am | Reply

    :-) 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:
    August 28, 2008 at 4:59 am | Reply

    You made my day

  10. Sunel Says:
    October 6, 2008 at 2:32 am | Reply

    Thank You.

  11. Russ Says:
    April 7, 2009 at 2:23 pm | Reply

    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:
    April 22, 2009 at 9:53 am | Reply

    Wow, this is the solution :D

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

  13. luke Says:
    May 26, 2009 at 11:27 am | Reply

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

  14. Naveen Says:
    June 2, 2009 at 6:21 pm | Reply

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

  15. Abraham Says:
    July 31, 2009 at 7:25 am | Reply

    Great!!!!!! That is perfect

  16. Dietrich Says:
    October 18, 2009 at 12:55 am | Reply

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

Leave a Reply