Post reply

SSIS Expressions in Derived Column component error on Nulling an inbound field

  • January 23, 2013 at 5:46 pm

    #267549

    I'd have sworn we had an SSIS section round these parts. Anyway.

    Version: 2k8R2 SSIS via VS2k8 interface to BIDS.

    Alright, short version. I've got a fixed width text file that leaves blanks for numbers in certain circumstances. I'm converting this from a larger process and it's a midpoint step.

    Fixed Width files cannot inbound a null value, as this field in particular is 16 characters, so it imports 16 blanks into the field. Can't make it numeric on inbound. So, I figure I'll Null them after import and then do a conversion. Simple enough.

    Or not.

    I get this lovely noise:

    Error at Data Flow Task [Quanity and Amount Nullifications [1555]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "TRIM(Quantity) == "" ? NULL(DT_STR,16,1252) : (DT_STR,16,1252)Quantity" 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 explicitly cast with a cast operator.

    ... other errors chaining off the error ...

    I have stared at this for the last 20 minutes and I cannot puzzle out, nor google, what the heck it's trying to tell me to do to get this to work.

    The datatype is being cast so I can include a NULL into the field. I know I've done this before.

    Anyone got an idea (or two)?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 23, 2013 at 5:56 pm

    #1580322

    I finally managed to get my google-fu going, but posting for resolution.

    The answer is.... (Hey, Maestro... drumroll please? Dude? Dammit)...

    TRIM( (DT_STR,16,1252)Quantity) =="" ? (DT_STR,16,1252)NULL(DT_STR,16,1252) : Quantity

    You have to cast the null as the datatype that you're telling it as one of its parameters.

    God I just love the Expression language.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply