SSIS expression fails to validate

3
votes

I have set a following expression in SSIS derived column component:

TRIM(xCOL) == "" ? (DT_STR,7,1252)xCOL : NULL(DT_STR,7,1252)

However, this fails to validate - I get the following error:

Error at PKG: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "TRIM(xCOL) == "" ? (DT_STR,7,1252)xCOL : NULL(DT_STR,7,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.

What is wrong here?

2
ssis

2 Answers

6
votes

Unicode - the bane of all SSIS things that you would think just work.

TRIM(xCOL) == "" ? (DT_STR,7,1252)xCOL : NULL(DT_WSTR, 7)

By default, expressions on strings have a return type of Unicode (DT_WSTR). You can prove this yourself by replacing your last expression with an empty string "". Look at at the type that the derived column is assigned as - it's a DT_WSTR

TRIM(xCOL) == "" ? (DT_STR,7,1252)xCOL :  ""

The resolution then is to cast the result of the entire expression to your desired type. (I also think you have a logic issue since I'm assuming you are inspecting the trimmed string to determine if it's empty/zero length and then casting it to NULL). Also note, that if you ever do get a NULL in for your xCOL field, this will blow up.

(DT_STR, 7, 1252)((TRIM(xCOL) == "") ? NULL(DT_WSTR, 7) :  TRIM(xCOL))

Source data

SELECT 'Hello' AS xCOL
UNION ALL SELECT ''

Results

enter image description here

4
votes

Like the error says, you must explicitly cast with a cast operator.

TRIM(xCOL) == "" ? (DT_STR,7,1252)xCOL : (DT_STR,7,1252)NULL(DT_STR,7,1252)