Post reply

Errors Exporting SQL to Excel xls

  • October 27, 2011 at 2:19 pm

    #250736

    I started creating an SSIS Package to export 139,313 Records to an Excel WorkSheet.

    Both the Client and the Server is running 64 bit OS.

    After several errors and exporting 66,899 rows the package fails due to the number of error.

    Any ideas would be greatly appreciated.

    Thanks.

    The errors are as follows:

    SSIS package "TxQuoteActivity.dtsx" starting.

    Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

    Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

    Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

    Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

    Error: 0xC0202009 at Data Flow Task, Excel Destination [16]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    Error: 0xC0209029 at Data Flow Task, Excel Destination [16]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (27)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (27)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    Error: 0xC0047022 at Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Error: 0xC0047021 at Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

    Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    Error: 0xC0047038 at Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.

    Error: 0xC0047021 at 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.

    Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

    Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

    Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Excel Destination" (16)" wrote 65535 rows.

    Task failed: Data Flow Task

    Warning: 0x80019002 at TxQuoteActivity: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    SSIS package "TxQuoteActivity.dtsx" finished: Failure.

    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/

  • October 27, 2011 at 2:23 pm

    #1400401

    This error

    component "Excel Destination" (16)" wrote 65535 rows.

    is too much of a coincidence. You must be writing to a pre-2007 version of Excel, which can support a maximum of 65535 rows.

  • October 27, 2011 at 2:33 pm

    #1400407

    Initially I had it set to an xlsx version.

    Then I got an error that I must specify an xls.

    I have since changed the file type and connection manager but I have another error.

    I belive that I have to select another provider.

    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/

  • October 28, 2011 at 12:55 am

    #1400506

    For .xlsx you need to use the ACE OLE DB provider. Normally this is automatically selected when you choose Excel 2007 in the Excel Connection Manager.

    Also make sure you are running the package in 32-bit, as the ACE and JET provider don't really support 64 bit.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 28, 2011 at 7:51 am

    #1400630

    Koen Verbeeck (10/28/2011)

    For .xlsx you need to use the ACE OLE DB provider. Normally this is automatically selected when you choose Excel 2007 in the Excel Connection Manager.

    Also make sure you are running the package in 32-bit, as the ACE and JET provider don't really support 64 bit.

    Thanks.

    I realized that I need the provider and I had to set the Advanced Extended Properties to Excel 12.0.

    I was able to export the Data to Excel successfully. This export on contained the first 4 columns of many that I needed to export to this particular WorkSheet.

    But when I tried a SQL Data Source that contained all of the Column, a large number, I get the following error Starting with the first column for every column.

    Column "Quoted" cannot convert between unicode and non-unicode string datatypes. The Columns in SQL are non-unicode.

    I tried changing the Data Types to non-unicode and I also tried the Data Conversion Task but obviously I'm missing something.

    I could cast the data types in SQL but what is the best approach and does anyone have a good example with a SQL Source and an Excel Destination?

    Any help would be greatly appreciated.

    Thanks guys for your help. 🙂

    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/

  • October 28, 2011 at 8:31 am

    #1400653

    The data conversion task should do the job - are you aware that it creates a new variable (so you need to change your column mappings to use what the DC task has converted)?

    Or are you sure that you can't just use a CSV file instead? Much easier.

  • October 28, 2011 at 8:45 am

    #1400660

    Phil,

    Thanks for getting back to me again.

    I want back to my original small column set but since I last successfully ran it, I had added the CONVEERT function on the Date Columns.

    I removed the the CONVERT, ran the package and it completed without error.

    If I run the package with all of the columns (100+) I get the unicode error on every column.

    It would be nice if I could output to a CSV File but the Workbook is huge.

    I have to populate several Worksheets.

    The Workbook also contains several tabs that consist of Pivot Tables.

    There is a lot of information in the Report Header and the columns heading usually don't start until row 7.

    Do you have any good links for the Data Conversion Task (with screen shots)?

    Thanks for your help. 🙂

    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/

  • October 28, 2011 at 11:46 am

    #1400739

    I have found that the Data Conversion Task is need for each column that use an Alias on.

    An Alias is needed on all most all of the columns to meet the requirements.

    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/

  • October 28, 2011 at 2:16 pm

    #1400785

    I have resolved the issue.

    The only things that I do not like is that by default it gives the Alias Name Copy of Column Name to every Column.

    You also have to specify the Data Conversion Source when mapping each column.

    I had to populate 8 Worksheets so it was a little time consuming. :w00t:

    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/

Viewing 9 posts - 1 through 9 (of 9 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy