I had some strange error today that took a good time to fix that I thought I'd post to prevent others from experiencing it. The problem occured with a Derived Column Transform when trying to do a conditional operator. I was trying to determine if the value in the column was blank, then convert it to NULL instead. The logic looked something like this:

TRIM(StringColumn) == "" ? NULL(DT_STR,12,1252) : TRIM(StringColumn)

Immediately, I got one of those cryptic error messages from the transform that resembled the one below:

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

The resolution was quite strange. When reading the error message over and over again, it didn't make sense. It was complaining that I should have to type cast to do a NULL but that's exactly what the (DT_STR) was doing. Also, if you removed the conditional operator, everything was fine. Out of shear dumb luck, I tried the following code (change is underlined in bold), which immediately worked.

TRIM(StringColumn) == "" ? (DT_STR,12,1252)NULL(DT_STR,12,1252) : TRIM(StringColumn)

Hope this helps someone!

-Brian Knight