March 11, 2008 at 6:35 pm
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
March 11, 2008 at 7:35 pm
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
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
March 11, 2008 at 9:14 pm
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
March 12, 2008 at 4:11 pm
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
September 21, 2008 at 9:58 am
I also did the same (excel-> access then sql server.). It worked.
September 22, 2008 at 11:02 am
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.
July 5, 2011 at 1:58 pm
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.
November 21, 2011 at 2:07 am
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.
March 19, 2012 at 12:07 pm
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/
March 19, 2012 at 12:33 pm
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
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
March 19, 2012 at 12:53 pm
Jack Corbett (3/19/2012)
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/
Sweet, thanks a lot.:cool:
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/
March 19, 2012 at 1:00 pm
Dumb question but where do I put the Script Component in the Data Flow?:unsure:
Don't I need to specify what to do with all of the columns?
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/
March 19, 2012 at 1:03 pm
After the destination
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
March 20, 2012 at 12:09 pm
Jack Corbett (3/19/2012)
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/
I noticed on your screen shot the first column that you have is errorsource?
Also for clarification what did you name your script component, errorstep or errorsource?
a. ErrorStep string(100) – this is going to be the Name of the Script Component which I name using the step which is the source of the error
b. ErrorDataDetails Unicode String(4000) – this is going to be an XML string that will have the data from the Input Columns and will be inserted into an XML Type column in SQL Server
c. ErrorDesc string(100) – The error description gotten from the ErrorCode
d. ErrorTask string(100) – The SSIS Task that the Step is in. In my case it is a DataFlow Task. This is retrieved using the TaskName package variable that is created by SSIS.
e. PackageTime Date – The start time of the package. This is retrieved using the StartTime package variable.
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/
May 28, 2015 at 4:36 pm
Save the .xls to a .csv file and then try importing that csv file. It worked for me.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply