Post reply

SSIS 2019 - Conditional Expression for the value of a variable- fails evaluation

  • September 21, 2020 at 12:57 pm

    #3789782

    Hi All,

    Ran into an error trying to evaluate the following expression for a variable value:

    @[User::varBadRows] > 0 ? (DT_STR, 50, 1252) ("There are " + (DT_STR, 3, 1252) @[User::varBadRows] + " rows with a non-matching SPID value.") : NULL(DT_STR, 50, 1252)

    I'm using Visual Studio 2019 Pro and my target environment is SQL Server 2019.   I get the following error:

    Expression cannot be evaluated
    For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "@[User::varBadRows] > 0 ? (DT_STR,50,1252)("There are " + (DT_STR,3,1252)@[User::varBadRows] + " rows with a non-matching SPID value.") : NULL(DT_STR,50,1252)" 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.

    Attempt to set the result type of conditional operation "@[User::varBadRows] > 0 ? (DT_STR,50,1252)("There are " + (DT_STR,3,1252)@[User::varBadRows] + " rows with a non-matching SPID value.") : NULL(DT_STR,50,1252)" failed with error code 0xC00470ED.

    Not sure why...   my objective is to either have a string value that shows the number of rows I found with a non-matching SPID value, or to have a NULL string value.   Is this not possible?   How do I adjust this to work?

    • This topic was modified 4 years, 2 months ago by  sgmunson. Reason: Fixed the first line of the post to make it clear the expression is for the value of a variable

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • September 21, 2020 at 1:09 pm

    #3789789

    Wow... talk about dumb things.... turns out you actually have to cast the NULL function, which already contains an explicit data type, to the DT_STR data type for the expression to work ...   sorry Microsoft, but what's the point of doing that?   Allegedly, you are already accomplishing that task via the data type specification for the NULL function.   Seriously?

    Here's the final working expression:

    @[User::varBadRows] > 0 ? (DT_STR, 50, 1252) ("There are " + (DT_STR, 3, 1252) @[User::varBadRows] + " rows with a non-matching SPID value.") : (DT_STR, 50, 1252) NULL(DT_STR, 50, 1252)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

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

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