derived column error

Question

derived column error

arkiboys

Hall of Fame

Points: 3958
More actions
January 15, 2016 at 2:13 am

#306932

Hello,
Using the foreachloop I loop through excel files to get the filename andthen start loading the data into sql server using oledb...
1- I get the filename
2- Then I strip the filedate from the filename and assign to the ssis-variable called FileDate which is of type string
3- I use this variable in the derived column - It is at this stage that I get the following error
[Derived Column [16]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[FileDate]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
note that in the derived column transformation editor, in the Derived column name I put the FileDate text, in expression column I put @[User::FileDate] then datatype column is automatically selected as Unicode string [DT_WSTR] and length column is automatically set to 0
can you see what is wrong please?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

arkiboys (1/15/2016)
Hello,
Using the foreachloop I loop through excel files to get the filename andthen start loading the data into sql server using oledb...
1- I get the filename
2- Then I strip the filedate from the filename and assign to the ssis-variable called FileDate which is of type string
3- I use this variable in the derived column - It is at this stage that I get the following error
[Derived Column [16]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[FileDate]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
note that in the derived column transformation editor, in the Derived column name I put the FileDate text, in expression column I put @[User::FileDate] then datatype column is automatically selected as Unicode string [DT_WSTR] and length column is automatically set to 0
can you see what is wrong please?

A unicode string of 0 length is likely to be a problem, any attempt to set a value will result in a truncation

😎

arkiboys Hall of Fame Points: 3958 More actions · Answer 2

Eirikur Eiriksson (1/15/2016)
arkiboys (1/15/2016)
Hello,
Using the foreachloop I loop through excel files to get the filename andthen start loading the data into sql server using oledb...
1- I get the filename
2- Then I strip the filedate from the filename and assign to the ssis-variable called FileDate which is of type string
3- I use this variable in the derived column - It is at this stage that I get the following error
[Derived Column [16]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[FileDate]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
note that in the derived column transformation editor, in the Derived column name I put the FileDate text, in expression column I put @[User::FileDate] then datatype column is automatically selected as Unicode string [DT_WSTR] and length column is automatically set to 0
can you see what is wrong please?
A unicode string of 0 length is likely to be a problem, any attempt to set a value will result in a truncation
😎

Hi,

Yes, I understand that but how can I solve this problem.

As you see in my post, the 0 length is set automatically and I can not change it.

thanks

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

arkiboys (1/15/2016)
Eirikur Eiriksson (1/15/2016)
arkiboys (1/15/2016)
Hello,
Using the foreachloop I loop through excel files to get the filename andthen start loading the data into sql server using oledb...
1- I get the filename
2- Then I strip the filedate from the filename and assign to the ssis-variable called FileDate which is of type string
3- I use this variable in the derived column - It is at this stage that I get the following error
[Derived Column [16]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Outputs[Derived Column Output].Columns[FileDate]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
note that in the derived column transformation editor, in the Derived column name I put the FileDate text, in expression column I put @[User::FileDate] then datatype column is automatically selected as Unicode string [DT_WSTR] and length column is automatically set to 0
can you see what is wrong please?
A unicode string of 0 length is likely to be a problem, any attempt to set a value will result in a truncation
😎
Hi,
Yes, I understand that but how can I solve this problem.
As you see in my post, the 0 length is set automatically and I can not change it.
thanks

Use a cast function to define the data type and length.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2