I am trying to null out a column if it is blank, but get an error in the
derived column transformation. Something about casting operands... but seems
illogical to me...


Column: tax_auth_group DT_STR(10)


Transform: TRIM(tax_auth_group) == "" ? NULL(DT_STR, 10, 1252) :
tax_auth_group


I get the following error:

TITLE: Microsoft Visual Studio
------------------------------

Error at Import Tax Corp Data [Derived Column [862]]: For operands of the
conditional operator, the data type DT_STR is supported only for input
columns and cast operations. The expression "tax_auth_group == "" ?
NULL(DT_STR,10,1252) : tax_auth_group" 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.

Error at Import Tax Corp Data [Derived Column [862]]: Attempt to set the
result type of conditional operation "tax_auth_group == "" ?
NULL(DT_STR,10,1252) : tax_auth_group" failed with error code 0xC00470ED.

Error at Import Tax Corp Data [Derived Column [862]]: Computing the
expression "tax_auth_group == "" ? NULL(DT_STR, 10, 1252) : tax_auth_group"
failed with error code 0xC00470A0. The expression may have errors, such as
divide by zero, that cannot be detected at parse time, or there may be an
out-of-memory error.

Error at Import Tax Corp Data [Derived Column [862]]: The expression
"tax_auth_group == "" ? NULL(DT_STR, 10, 1252) : tax_auth_group" on "input
column "tax_auth_group" (947)" is not valid.

Error at Import Tax Corp Data [Derived Column [862]]: Failed to set property
"Expression" on "input column "tax_auth_group" (947)".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

Can you try this instead

TRIM(tax_auth_group) == "" ? NULL(DT_WSTR, 10) : tax_auth_group


--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Brandon Lilly" <avarice (AT) nospam_swbell (DOT) net> wrote

That works, although I am at a loss as to why, since DT_WSTR is different
than the resulting datatype DT_STR. Is this a bug that the ISNULL(DT_STR...)
doesn't work?

Thanks!

Brandon

"Allan Mitchell" wrote:

You can use this type conversion in the conditional expression but the
output datatype can still be DT_STR

--


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com


"Brandon Lilly" <avarice (AT) nospam_swbell (DOT) net> wrote