CODEWITHSUNDEEP
ssiscastingconditional-statements
e_i_pi
e_i_pi

Reputation: 4820

SSIS conditional expression not handling conditional cast to NULL DT_STR

I'm having a little trouble with an SSIS expression where, in a Derived Column Transformation Data Flow Task, I am attempting to grab a 6 character substring from a string input, casting the derived columns value to NULL if it doesn't exist. This is the code I am using, with line breaks and indentation added for readability:

KeyValueLength == -2 ?
    NULL(DT_STR,6,65001) :
    (
        KeyValueLength == -1 ?
        (DT_STR,6,65001)RTRIM(SUBSTRING(StringInput,KeyValueStart,999)) :
        (DT_STR,6,65001)SUBSTRING(StringInput,KeyValueStart,KeyValueLength)
    )

(For reference, when KeyValueLength is -2 the key value is not found, when it is -1 then it is found at the end of StringInput, any other number and it is found in the middle of StringInput. This code works for other key values I'm getting that are casting to DT_I4 and DT_DECIMAL)

Individually, the following three expressions do not generate an error:

NULL(DT_STR,6,65001)

(DT_STR,6,65001)RTRIM(SUBSTRING(StringInput,KeyValueStart,999))

(DT_STR,6,65001)SUBSTRING(StringInput,KeyValueStart,KeyValueLength)

But when put together in that nested conditional above, I get the following error when trying to save the window:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "KeyValueLength == -2 ? NULL(DT_STR,6,65001) : (KeyValueLength == -1 ? (DT_STR,6,65001)RTRIM(SUBSTRING(StringInput,KeyValueStart,999)) : (DT_STR,6,65001)SUBSTRING(StringInput,KeyValueStart,KeyValueLength))" 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.

I'm having a little trouble figuring out exactly what the issue is here. That error message suggests it's to do with the use of conditionals, but I'm not seeing the problem.

Upvotes: 1

Views: 725

Answers (1)

e_i_pi
e_i_pi

Reputation: 4820

So, in the infinite wisdom of Microsoft, this is null as a DT_STR and perfectly valid as a direct value assignment:

NULL(DT_STR,6,65001)

But if you want to assign that value in a conditional where all eventual conditions must be the same type you have to do this:

(DT_STR,6,65001)NULL(DT_STR,6,65001)

The same does not apply for other types, where something like NULL(DT_I4) is valid irrespective of whether it is directly assigned or assigned via condition. SMH

Upvotes: 2

Related Questions