July 17, 2012 at 3:22 pm
Hi All,
I have installed new SQL Server 2008 R2 including BIDS. Now I have to upgrade all the packages in 2005 to 2008 and make sure they run fine.
I have migrated one package and upgraded to 2008 and ran the package successfully on BIDS but, when i scheduled the package and try to run it under SQL Agent i am getting error mostly because of one column parent_office with varchar(10) on SSMS. I am sure the file which gets delivered to us from oracle also has same datatype with same length.
Can anyone help me with the error below:
Executed as user: CFS\SQLServer. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:16:05 PM Error: 2012-07-17 16:16:06.61 Code: 0xC02020A1 Source: Data Flow Task Flat File Source [1] Description: Data conversion failed. The data conversion for column "parent_office" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2012-07-17 16:16:06.61 Code: 0xC020902A Source: Data Flow Task Flat File Source [1] Description: The "output column "parent_office" (1219)" failed because truncation occurred, and the truncation row disposition on "output column "parent_office" (1219)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2012-07-17 16:16:06.61 Code: 0xC0202092 Source: Data Flow Task Flat File Source [1] Description: An error occurred while processing file "c:\CFS\Oracle\master.DAT" on data row 45329. End Error Error: 2012-07-17 16:16:06.61 Code: 0xC0047038 Source: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. 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. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:16:05 PM Finished: 4:16:06 PM Elapsed: 0.952 seconds. The package execution failed. The step failed.
I have searched Google and found that one guy resolved the issue by installing new service pack in 2005. I have got the error in 2008 and we had no problem in 2005 because the package is running fine.
Can anyone please tell me the solution?
July 18, 2012 at 8:33 am
Did you install SQL Server 2008 with the same collation as the 2005 server?
Have you verified that source definition hasn't changed to allow more characters and thus the conversion is causing truncation?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 18, 2012 at 9:14 am
Jack Corbett (7/18/2012)
Did you install SQL Server 2008 with the same collation as the 2005 server? --I didn't install with same collation. How do i do it now?Have you verified that source definition hasn't changed to allow more characters and thus the conversion is causing truncation?
I didn't install with same collation. How do i do it now?
Yes, I have verified and the source hasn't change its definition.
July 18, 2012 at 9:27 am
I have tried to use this to find the collation on both servers.
Select SERVERPROPERTY(N'Collation')
Old SQL Server 2005 gave this: SQL_Latin1_General_CP1_CI_AS
New SQL Server 2008 r2 gave this: SQL_Latin1_General_CP1_CI_AS.
I believe they are using same collation.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply