locked
Issue to export data from sql to excel RRS feed

  • Question

  • I have MS SQL 2008 Developer version and visual studio 2008. I'm using SSIS Import and Export Wizard on the VS2008 to create a simple package to export data from a table using a sql query to excel file (.xlsx), but I got the following error messages:

    [Destination - Query [37]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

    [Destination - Query [37]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

    [SSIS.Pipeline] Error: component "Destination - Query" (37) failed the pre-execute phase and returned error code 0xC0202025.

    The SQL query is

    SELECT [BusinessEntityID]
          ,[PersonType]
          ,[NameStyle]
          ,[Title]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks2008].[Person].[Person]

    Any help will be appreciated. Thanks.


    A Fan of SSIS, SSRS and SSAS

    Friday, January 30, 2015 9:29 PM

Answers

All replies

  • You need to review the datatypes. Excel is Unicode so any textual columns need to be so

    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Wendy Fu Monday, February 2, 2015 1:20 PM
    • Marked as answer by Wendy Fu Tuesday, February 10, 2015 3:01 AM
    Friday, January 30, 2015 10:00 PM
  • When you put the Query and click Next, you have option there as "Edit Mappings". In there, you need to map columns by correcting data types for Excel Destination as below:


    -Vaibhav Chaudhari

    • Proposed as answer by Wendy Fu Monday, February 2, 2015 1:20 PM
    • Marked as answer by Wendy Fu Tuesday, February 10, 2015 3:01 AM
    Saturday, January 31, 2015 12:28 PM
  • Or another way is to save the package created by Export Import wizard, open it in BIDS and add a Derived column task before the Excel destination to do explicit casting of the columns to your required unicode datatypes.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Wendy Fu Monday, February 2, 2015 1:20 PM
    • Marked as answer by Wendy Fu Tuesday, February 10, 2015 3:01 AM
    Saturday, January 31, 2015 4:53 PM
  • I'm not sure about that specific error, but why don't you try another option.  Use VBA to move the data from SQL Server to Excel.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Wendy Fu Tuesday, February 10, 2015 3:02 AM
    Monday, February 2, 2015 8:20 PM
  • Hi Vaibhav,

    I am not able to edit the mappings. Any idea about that? Thanks.


    A Fan of SSIS, SSRS and SSAS

    Monday, February 2, 2015 8:58 PM
  • Maybe you can try '.xls'.  That's older technology.  See this.

    http://help.pragmaticworks.com/dtsxchange/scr/FAQ%20-%20How%20to%20run%20SSIS%20Packages%20using%2032bit%20drivers%20on%2064bit%20machine.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Wendy Fu Tuesday, February 10, 2015 3:02 AM
    Monday, February 2, 2015 9:19 PM
  • I don't think we can edit the mappings. So Visakh16's is the another method to resolve the error. 

    Thanks, hsbal

    Monday, February 2, 2015 9:27 PM
  • Hi Vaibhav,

    I am not able to edit the mappings. Any idea about that? Thanks.


    A Fan of SSIS, SSRS and SSAS

    I am not sure why it is not editable for you.

    Actually I tried exporting it by myself. I used the query what you have in OP and edited mappings and records were successfully exported. My file name was New.xls.

    Just try to edit mappings from wizard or from BIDS, in the end you just have to correct datatypes and it should work then.


    -Vaibhav Chaudhari

    Tuesday, February 3, 2015 5:14 AM
  • I do not know why I am not able to edit the mappings. So as ryguy72 suggested, I changed the xlsx to xls and it worked.

    A Fan of SSIS, SSRS and SSAS


    Tuesday, February 3, 2015 5:13 PM