Derived Column Editor

Question

Hi

I need to assign a formatted date to a column in a data flow. I have added a Derived shape and entered the following expression for a NEW column - Derived Column = "add as new column":

"BBD" + SUBSTRING((DT_WSTR,4)DATEADD("Day",30,GETDATE()),1,4) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),6,2) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),9,2)

The problem is that the "Derived Column Transformation Editor" automatically assigns a Data Type of "Unicode string[DT_WSTR]" and a length of "7". Howver, the length of a string is 11, therefore the following exception is thrown each time:

[Best Before Date [112]] Error: The "component "Best Before Date" (112)" failed because truncation occurred, and the truncation row disposition on "output column "Comments" (132)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Does anyone know why the edit is insisting on a length of 7? I don't seem to be able to change this.

Many thanks,

Rob.

Answer 1

Are you replacing your existing field, and is that field possibly 7 chars long? The thing with the Derived Column Transform is that you can't change the field types (including length) of the existing fields.

Try to add a new field instead.

If that's not working, try adding an explicit cast around the whole expression.

(DT_WSTR,11)("BBD" + SUBSTRING((DT_WSTR,4)DATEADD("Day",30,GETDATE()),1,4) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),6,2) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),9,2))

Answer 2

What release and service pack of SQL are you using?

I just tried this on my machine and had no problems changing the result size from 7 to 11. Is it possible that you have not installed all the service packs?

Answer 3

I can't understand why SSIS is measuring that column as only resulting in a seven character field - but to force it to provide an 11 character column for it, modify your expression slightly to this:

(DT_WSTR, 11)("BBD" + SUBSTRING((DT_WSTR,4)DATEADD("Day",30,GETDATE()),1,4) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),6,2) + SUBSTRING((DT_WSTR,2)DATEADD("Day",30,GETDATE()),9,2))