Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS - 2008: Truncation may occur due to retrieving data from database columns : Error Expand / Collapse
Author
Message
Posted Sunday, June 15, 2014 8:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:39 AM
Points: 41, Visits: 176
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.

Post #1580978
Posted Sunday, June 15, 2014 11:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1580993
Posted Monday, June 16, 2014 3:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:39 AM
Points: 41, Visits: 176
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..
Post #1581041
Posted Monday, June 16, 2014 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1581042
Posted Monday, June 16, 2014 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:39 AM
Points: 41, Visits: 176
Yes, cannot modify in destination..
Post #1581045
Posted Monday, June 16, 2014 4:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1581051
Posted Monday, June 16, 2014 4:27 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:28 AM
Points: 579, Visits: 914

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/
Post #1581052
Posted Monday, June 16, 2014 4:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Wednesday, December 24, 2014 6:46 AM
Points: 13,646, Visits: 11,524
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1581055
Posted Monday, June 16, 2014 8:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:39 AM
Points: 41, Visits: 176
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.

Post #1581158
Posted Monday, June 16, 2014 9:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:39 AM
Points: 41, Visits: 176
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..
Post #1581196
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse