Thursday, November 22, 2012

Return a NULL DT_STR in a conditional statement

Return a NULL DT_STR in a conditional statement

I have a requirement to convert all blank strings in a data flow into NULLs.  Here is the expression I tried first:
TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]
This threw an error:
Error at Master DataFlow [Derived Column Task]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations.  The expression “TRIM( [Some Column] ) == “” ? NULL( DT_STR, [length], 1252) : [Some Column]” 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 explicitily cast with a cast operator.
After much messing around, I foud that if I cast the NULL statement as a DT_STR, the expression compiler was happy:
TRIM( [Some Column] ) == “” ? (DT_STR, 8, 1252)NULL(DT_STR, [length], 1252) : [Some Column]
Is this an expected behavior of Integraion Services?
I guess I would have expected that since I specified the type in the NULL statement that I would have to cast it as a DT_STR afterwords.
Any thoughts?