locked
Exporting to 32 Bit Excel in 64 Bit Enviroment RRS feed

  • Question

  • Hi All,

    I have recently upgraded from SQL Server 2005 to SQL Server 2008 R2 running on Server 2008 R2 64 bit.

    I have over 100 SQL jobs from SQL 2005 and i am setting them up in the new enviroment. They all select rows from the SQL tables and dump them in excel files which are then FTP to another server.

    My question is, once i selected the 32 bit switch in the jobs and in the SSIS projects they all run fine and the data is exported correctly. But I get an error when I select the preview button on the excel destitnation, error "There was an error displaying the preview".

    Now from what I have read this is due to 32 bit excel in 64 bit server enviroment. Also due to the contstraints of the application loading the files they have to be kept as .xls 2003 worksheet.

    Is there a way to view the .xls excel data in BIDS 2008(64 bit server)?

    Thanks

    Friday, May 30, 2014 3:30 AM

Answers

  • Finally found the answer.

    Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive” argument specified.

        To install the Microsoft ACE OLEDB Provider 32-bit on a machine running Office 2010 64-bit:

    $> AccessDatabaseEngine.exe /passive

        To install the Microsoft ACE OLEDB Provider 64-bit on a machine running Office 2010 32-bit:

    $> AccessDatabaseEngine_X64.exe /passive

    Thanks Carl from his blog.

    http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

    • Marked as answer by Cameronh Tuesday, July 29, 2014 1:35 AM
    Tuesday, July 29, 2014 1:34 AM

All replies

  • Hi ,

      BIDS environment is available only in 32 Bit. You will not be able to preview the excel using 64 bit driver as there will be a bit mismatch. But you will be able to execute the package using 64 bit driver by setting Run64BitRuntime property. Refer to the below link for more details

    http://technet.microsoft.com/en-us/library/ms141766.aspx


    Best Regards Sorna

    • Proposed as answer by Mike Yin Thursday, June 5, 2014 8:56 AM
    • Marked as answer by Mike Yin Sunday, June 8, 2014 4:24 PM
    • Unmarked as answer by Cameronh Tuesday, July 29, 2014 1:34 AM
    Friday, May 30, 2014 9:04 AM
  • Finally found the answer.

    Launching the install of a Microsoft ACE OLEDB Provider on a machine with an Office install other than the current one (e.g. 32 on 64) will cause the install to fail. To have it run properly you need to launch it from a command line with the “/passive” argument specified.

        To install the Microsoft ACE OLEDB Provider 32-bit on a machine running Office 2010 64-bit:

    $> AccessDatabaseEngine.exe /passive

        To install the Microsoft ACE OLEDB Provider 64-bit on a machine running Office 2010 32-bit:

    $> AccessDatabaseEngine_X64.exe /passive

    Thanks Carl from his blog.

    http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

    • Marked as answer by Cameronh Tuesday, July 29, 2014 1:35 AM
    Tuesday, July 29, 2014 1:34 AM