Post reply

SSIS and Excel - 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

  • April 23, 2015 at 10:04 am

    #317021

    I have an Excel file which I need to import into a scratch table, but I will need to do this repeatedly, until I get a final version, I am just developing some processing.

    My first attempt has been to use the Tasks | Import Data ... wizard from SSMS and set up all the column mappings. This worked fine although it is quite slow to do, so I saved the package thinking I would re-run it. However on trying it a) directly on the dev server (as sa in Integration Services) or b) locally on my PC where I originally ran it (through VS 2010), I get The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

    However I use this provider for several other import tasks behind web sites, it is always fine (and still is). My PC in Windows 8.1 64 bit, the DB server Windows 2012. I have installed the AccessDatabaseEngine_x64.exe on the server BTW.

    Has anyone got any ideas why it works in SSMS through the Import Data wizard but not either locally or remotely thereafter?

    Would I perhaps be better of just saving to a CSV as remapping is quite tiresome and I'd like a repeatable process?

  • April 23, 2015 at 2:39 pm

    #1793127

    I recently had some issues with this and had to also download the 2007 Office System Driver: Data Connectivity Components .

    Not sure if this will fix your issue but thought I'd share.

  • April 24, 2015 at 3:20 am

    #1793206

    I'm probably just going to do this via Access - I don't need the headache right now, thanks though.

  • April 24, 2015 at 3:43 am

    #1793210

    I actually managed to resolve this in the end, the package needed to be executed (debugged) in 32 bit mode.

    http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

    HTH.

  • June 7, 2024 at 1:25 am

    #4426748

    So I had this issue. Reading and writing with Excel was working fine until MS Office got completely removed from my work laptop, because we now use Google Workspace. It was a total disaster! I tried downloading the right driver and it's not really available anymore, so I tried something else that was just so simple. I set the Excel Destination to use a later version of Excel. DUH. So simple.

Viewing 5 posts - 1 through 4 (of 4 total)

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