SSIS Basic data conversion function

Question

Consider the following statement in a Derived Column Transformation:

Derived Column Name:

EFFECTIVE_DATE

Expression:

    TRIM([EFFECTIVE DATE]) == "" ? (DT_WSTR,255)NULL(DT_WSTR,255) : [EFFECTIVE DATE]

I read this as:

"If Trim of Effective Date is an empty string then assign EFFECTIVE_DATE NULL converted to a Unicode string of length 255. Otherise, assign EFFECTIVE DATE its current value."

I assume that this is what the code is doing, but I am confused about the syntax of the following:

(DT_WSTR,255)NULL(DT_WSTR,255)

I only expected:

(DT_WSTR,255)NULL

I thought that the leading (DT_WSTR,255) was a cast of the value that immediately follows, a NULL value. Why is there another (DT_WSTR,255) immeditely after. What I am missing?

Matt Gibson Matt Gibson · Accepted Answer · 2013-07-01T13:14:52

That expression, per se, looks a bit redundant, as:

NULL(DT_WSTR,255)

...means "Generate a NULL value of type DT_WSTR with length 255".

So it looks like:

(DT_WSTR,255)NULL(DT_WSTR,255)

...means "Cast a NULL value of type DT_WSTR with length 255 to type DT_WSTR with length 255".

So it therefores seems at face value as if the type cast actually does nothing. However, if you leave it off, you may run into a problem as described here, which is that you'll experience this error at runtime:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING([string-col],"",1) == 0 ? [string-col] : 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.

This seems like an oddly arbitrary limitation, but it exists, so you need the cast.

SSIS Basic data conversion function

1 Answers