CODEWITHSUNDEEP
ssis
Rob Bowman
Rob Bowman

Reputation: 8741

Derived Column Editor

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.

Upvotes: 2

Views: 11452

Answers (5)

Web Developer India
Web Developer India

Reputation: 677

Right click on "Derived Column" open "Show Advanced Editor" Select "Input and output Properties" tab.
Got to "Derived column output" => "Output Columns" => "Derived Column 1" (added by you)

In right side panel go to "Data type Properties" section=> DataType=>

Select "String [DT_STR]

click OKImage showing steps

This will solve your problem.

enter image description here

Upvotes: 1

Zusukar
Zusukar

Reputation: 382

What you want is:

"BBD" + (DT_WSTR,4)YEAR(DATEADD("Day",30,GETDATE())) 
+ RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("Day",30,GETDATE())),2) 
+ RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("Day",30,GETDATE())),2)

The issue is in how you are converting your dates to a string. The calls to DATEADD return a full date & time. Next, you then have either (DT_WSTR,4) or (DT_WSTR,2) to convert that date into either a 4 or 2 character string. On my system, converting a datetime to a string defaults to "Aug 24 2011 4:18PM". So the first 4 characters gets you "Aug " and the first 2 characters gets you "Au". Then, you are extracting substrings using SUBSTRING. For your last two calls to SUBSTRING, you are starting the substring past the end of the 2 character string you converted the date into. This is why SSIS displays 7 characters:

"BBD" + "Aug " + "" + ""
  3   +    4   +  0 +  0  =  7

It is better to use the built in functions to extract the Year, Month and Day from a datetime rather than converting to a string and then grabbing substrings. If you really wanted to use substrings, you would need to add a call to CONVERT to get the datetime to a specific string format, otherwise you will get whatever the default is for your locale setting in Windows. This could be different on each PC.

Upvotes: 1

Valentino Vranken
Valentino Vranken

Reputation: 5815

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))

Upvotes: 0

Todd McDermid
Todd McDermid

Reputation: 1680

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))

Upvotes: 5

William Anderson
William Anderson

Reputation: 1

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?

Upvotes: 0

Related Questions