Conditional Expression quirk?

When using the conditional expression in a derived column transformation, I found that the following expression:

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : [F1Name]

is invalid while

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : "" + [F1Name]

is valid.

In both cases, the output type is set to Unicode String (DT_WSTR) with 4000 characters. The error in the first case is:

Error at Data Flow Task [Derived Column (2784)]: Failed to set property "Expression"on "output column" "FactorName" (2918).

Would this be considered a bug, or is there a reasonable explanation?

Thanks,

Anna.

Some more information: The columns F1Name & F2Name are output columns from a lookup that is based on a SELECT statement and their underlying data type/length are varchar (7900)

It seems like the Expression compiler is unable to map this varchar length to any data type?

|||

Is it possible that you have NULL data?|||Expressions are limited to 4000 characters.|||

Phil,

Thanks for your response. However, adding an empty string before the string column makes the expression valid. Why is that?

Thanks,

Anna.

|||Could it have something to do with the ANSI to Unicode conversion? If you explicitly cast F1 to Unicode, does it work?

|||

Thanks for the suggestion.

Either of these works fine:

(DT_STR, 4000, 1252) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to string [DT_STR]

or

(DT_WSTR, 4000) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to Unicode string [DT_WSTR]

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I do get a warning about the length in both cases, but that is not really an issue.

Is this related more to the length of the source column and the 4000 character restriction in the expression?

|||

Annapurni wrote:

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I think you hit the problem right there - In your original code, it thinks you are trying to return a DT_WSTR, but you are returning a DT_STR. Since any literal string value is interpreted as Unicode by SSIS, appending an empty string is forcing the conversion of the DT_STR to a DT_WSTR.

I think. Not 100% positive, though.