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

Error 0xc020901c: Data Flow Task: While Importing data from an Excel File into SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2008 6:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2008 4:09 PM
Points: 3, Visits: 4
While importing data from Excel file into a table in SQL Server 2005, i am getting the following error.

- Executing (Error)
Messages
Error 0xc020901c: Data Flow Task: There was an error with output column "Description" (30) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Description" (30)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (30)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Risks$" (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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)


Any help much appreciated.

Thanks
Post #467840
Posted Tuesday, March 11, 2008 7:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
I suggest configuring the error output to redirect row and outputting the data to a table or file so you can see what row(s) are being rejected. You are currently only seeing 1 problem, there could be more and redirection will allow you to see what other problems you may have.

The error is probably due to a strange character in your data and redirecting the output will allow you to find it. Have you checked the length of your source data in Excel? Are you inserting into char or varchar column? If char are you trimming the data to insure you do not have trailing spaces making it too long?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #467858
Posted Tuesday, March 11, 2008 9:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2008 4:09 PM
Points: 3, Visits: 4
Hi Jack,

I am inserting the values into a nvarchar(2000) column. All i am doing is, right click the database, select the import option, select the source and destination then do the import. I tried to follow your suggestions, but i couldn't figure out what exactly i have to do. i am completely new to SQL 2005 server. i am not using SQL SBID studio. Any other go?

Thanks
Priya
Post #467883
Posted Wednesday, March 12, 2008 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2008 4:09 PM
Points: 3, Visits: 4
I found a work around for this problem. I imported the data into Access database first and then imported it into SQL database successfully.

Thanks
Priya
Post #468421
Posted Sunday, September 21, 2008 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 10, 2012 9:34 AM
Points: 20, Visits: 55
I also did the same (excel-> access then sql server.). It worked.
Post #573144
Posted Monday, September 22, 2008 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 6:20 PM
Points: 25, Visits: 167
We have an application that uses the DTS infrastructure to import data from a variety of ODBC accessible data sources into a sqlServer warehouse repository.

Excel spreadsheets have always been a pain in the datatype and column name category when the same worksheet is used to import data over and over. I find the datatypes (and type control) in Excel is simply too fluid to be use for anything more than "one time" imports and even then, you're still likely to run into data type problems.

I always try to find a way to get Worksheets into something more "stable" before attempting any sort of data manipulation on them.
Post #573714
Posted Tuesday, July 5, 2011 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2012 1:51 PM
Points: 2, Visits: 37
2 years later but same thing happened to me. Had to go from Excel ->Access -> SQL.

Directly from Excel to SQL did not work for the life of me. Tried all the hacks including the registry, getting column length to max. No idea why.
Post #1136800
Posted Monday, November 21, 2011 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 1:15 AM
Points: 1, Visits: 39
I had the same problem with a DB2-source.

Problem is: The datasourche object replace the character that have no match in the codepage with a ? and gives a truncation error.

Solution:
Change with the Advanced Editor the Output Column propertie from RD_FailComponent to RD_IgnoreFaiure.
=> OLE-db Sourche object => Advanced Editor => [Input and Output Properties] => [Output Columns] => Choose the field with truncation error => Change propertie 'TruncationRowDisposition' to RD_IgnoreFaiure.
Post #1209085
Posted Monday, March 19, 2012 12:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 4,175, Visits: 4,257
Jack Corbett (3/11/2008)
I suggest configuring the error output to redirect row and outputting the data to a table or file so you can see what row(s) are being rejected. You are currently only seeing 1 problem, there could be more and redirection will allow you to see what other problems you may have.

The error is probably due to a strange character in your data and redirecting the output will allow you to find it. Have you checked the length of your source data in Excel? Are you inserting into char or varchar column? If char are you trimming the data to insure you do not have trailing spaces making it too long?


Jack,

I'm getting the same error when importing the data from DB2 AS400.

Do you have link to a simple exampl on redirecting output on error?


Thanks.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1269151
Posted Monday, March 19, 2012 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:36 PM
Points: 11,140, Visits: 12,881
Welsh Corgi (3/19/2012)

Jack,

I'm getting the same error when importing the data from DB2 AS400.

Do you have link to a simple exampl on redirecting output on error?


Thanks.


Here's an article on how I did it, http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62662/




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1269161
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse