Error 0xc020901c: Data Flow Task: While Importing data from an Excel File into SQL Server 2005

  • priyap

    SSC-Addicted

    Points: 419

    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

  • Jack Corbett

    SSC Guru

    Points: 184381

    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

  • priyap

    SSC-Addicted

    Points: 419

    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

  • priyap

    SSC-Addicted

    Points: 419

    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

  • Aur_Ek_SQL

    SSC Veteran

    Points: 252

    I also did the same (excel-> access then sql server.). It worked.

  • jchapman

    SSC Eights!

    Points: 985

    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.

  • shahrouz.ebadian

    Valued Member

    Points: 58

    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.

  • laurens.schepers

    SSC Journeyman

    Points: 85

    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.

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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/

  • Jack Corbett

    SSC Guru

    Points: 184381

    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

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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/

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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/

  • Jack Corbett

    SSC Guru

    Points: 184381

    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

  • Welsh Corgi

    SSC Guru

    Points: 116520

    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/

  • shammr_pes

    Valued Member

    Points: 69

    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 18 total)

You must be logged in to reply to this topic. Login to reply