Friday, January 18, 2019

SQL*Server Import Export Wizard 2017 vs Office 64 bit

Not Oracle but since we're having to multi-platform these days might be relevant to some.

When you install SQL*Server Management Studio on 64 bit windows the Management Studio (SSMS, equivalent of SQL*Plus) is 64 bit but it installs the 32 bit version of Import Export Wizard (SSIS).  This is fine if you have the 32 bit version of Office installed or only import and export CSV or other non-Microsoft file types.

If you have 64 bit Office and try to import an Excel spreadsheet then you will get an error message: "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)"

Googlign for this error message brings back a range of solutions (many dating back to SQL*Server 2008 and Office 2010) ranging from installing the 64 bit Access Runtime redistributable to setting up a VM in Oracle Virtual Box and putting the SSMS/SSIS software on that but not any form of Office.

When I ran into this error today we found, after some trial and error that the solution was to install the 32 bit Access Runtime redistributable:
When downloading make sure to select the 32 bit version.

If viewing other solutions  be wary of the date of the article, we went down a lot of rabbit holes due to articles from 2010/11.

This entry was written on 18th January 2019, if you're reading it much after that then you may be facign a different situation.