Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of
my table to an actual NULL value by using the Derived Column Transformation
tool. I have tried to use two different forms of a conditional operator to
achieve this end. However I am getting the below errors both can be summed
up with the following statement.

DT_STR operand cannot be used with the conditional operation.

The expression directly below however is using a type DT_I4 in the
conditional clause as this is what FINDSTRING returns. So I must say I am
somewhat confused by this. Does anyone know why neither of the below
statements are working?

Also is there an easy way to accomplish what I am trying to do - convert the
string "(null)" in the [PASSWORD] column of my table to an actual NULL value?



FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR,
255, 1252)

Error at Administrator Data Flow Task [Derived Column [1985]]: For operands
of the conditional operator, the data type DT_STR is supported only for input
columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1)
== 0 ? PASSWORD : NULL(DT_STR,255,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.



LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR,
255, 1252)

Error at Administrator Data Flow Task [Derived Column [1985]]: For operands
of the conditional operator, the data type DT_STR is supported only for input
columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) !=
"(null)" ? PASSWORD : NULL(DT_STR,255,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.




--
Thank you,
John

Hello John,
The error is telling you what to do. You need to explicitly cast the NULL
function , even though you state the data type in the NULL function i.e

(DT_STR, 255, 1252)NULL(DT_STR, 255, 1252)



Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons