SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - 2008: Truncation may occur due to retrieving data from database columns : Error


SSIS - 2008: Truncation may occur due to retrieving data from database columns : Error

Author
Message
rangu
rangu
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 208
Hi,
I am loading data from Ms access DB to the SQl Server 2008, using SSIS 2008.

I am getting below warning message while executing the package and eding up loading half of the data nd failing the package.

Truncation may occur due to retrieving data from database columns

I have given proper data type lengths, also the incoming data donot have the data beyond the actual lenght, this is verified.


Source file, MS access.. I heard Imex =1 is a solution, but looks like that works only for Excel.


What is the solution, stuck here.. Need help asap.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
The actual error most likely has the column name in it, and that's the one you should investigate.

What is the length in the source? What is the length in the destination?
What is the data type in the SSIS data flow?

At which component do you get this error?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rangu
rangu
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 208
Hi Koen,
Thanks for responding..
Yes I know the column name on which erroris occurring.
the datatype for this column nd length are matching.. I have tried fixing column length, like increasing as I mentioned earlier nothing worked..

The error is occurring in the first step at the source oledb step..
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
rangu (6/16/2014)
Hi Koen,
Thanks for responding..
Yes I know the column name on which erroris occurring.
the datatype for this column nd length are matching.. I have tried fixing column length, like increasing as I mentioned earlier nothing worked..

The error is occurring in the first step at the source oledb step..


Where did you increase it? In the SSIS data flow?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rangu
rangu
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 208
Yes, cannot modify in destination..
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
rangu (6/16/2014)
Yes, cannot modify in destination..


And you changed this in the advanced editor of the source component?

And you are sure it is a truncation error, not a code page error? (sometimes both errors are bundled in one error message. I wouldn't know if that is the case here, since you never posted the actual error message)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1726 Visits: 1099

I am getting below warning message while executing the package and eding up loading half of the data nd failing the package.

Truncation may occur due to retrieving data from database columns


So what is the error ?
Can you please post the error message as it may be a warning message but not always error ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63538 Visits: 13298
rhythmk (6/16/2014)

I am getting below warning message while executing the package and eding up loading half of the data nd failing the package.

Truncation may occur due to retrieving data from database columns


So what is the error ?
Can you please post the error message as it may be a warning message but not always error ?


Nicely spotted.
It's a warning, not an error.

The only way you can get rid of this warning message is when the length of the column in the data flow is smaller or equal than the column width of the destination column.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rangu
rangu
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 208
Before Executing the Package:
[Source FCM File [1]] Warning: Truncation may occur due to retrieving data from database column "Client_Short_Name" with a length of 100 to data flow column "Client_Short_Name" with a length of 60.

During the Execution of the Package:
[Source FCM File [1]] Error: There was an error with output column "Client_Short_Name" (7027) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[Source FCM File [1]] Error: The "output column "Client_Short_Name" (7027)" failed because truncation occurred, and the truncation row disposition on "output column "Client_Short_Name" (7027)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source FCM File" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
rangu
rangu
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 208
Ok, I could figure it out..

I went to Source OLEDB, Advanced Editor and Input and Output parameters.

There I have compared the length of the column in External Columns and Output columns.
External Column has length 100 which is coming from Access is set to 100.
Output Column was set to 60, strange I never changed that , is it default setting in SSIS?
I have changed this length to 100, it worked like charm.

Thanks Keon and rhty for your thoughts..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search