We have a SSIS package which is using a Excel destination. It was brought to my notice that the job was failing with the common error message 'Microsoft.ACE.OLEDB.12.0' is not registered. I checked the server and could locate this driver in the registry. I exported the package into my local and execute it and it ran successfully. I thought of checking to rebuild the package again and test it from the server. Is there any other idea/solution? Please suggest.
Sql-server – SSIS ACE.OLEDB.12.0 Driver issue
excelsql serverssis
Related Solutions
Is it possible to get the file as a .csv file which can be imported like a text file and will not have this problem? I generally kick back all Excel files and ask for another format because SSIS and Excel do not play well together on many levels.
I have just had the same problem yesterday, and here is how I was able solve it:
Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio),
the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there.
After I added the key to the registry, I was able to run the Query with a non-admin user as well.
Related Question
- Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler
- SSIS Excel Connection Manager Error – Resolve Error 0xC0209303
- SSIS Package – Fix ‘Subquery Returned More Than 1 Value’ Error
- Firebird ODBC Drivers Installation Issue – Driver Not Found
- SQL Server – SSIS Package Shows Completed Successfully but Does Nothing When Executed via Agent Job or Manually
- Sql-server – Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination
- Sql-server – Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
Best Answer
A common cause of this problem can be the difference between 32 bit and 64 bit installations.
If your SSIS package is running in 64 bit the Access driver needs to be installed in 32 bit too.
My guess would be that on your machine you installed the 64 bit driver, but on the server the 32 bit driver has been installed.
You could test this by executing the package from the 32 bit dtexec found here:
And trying to execute it from the 64 bit dtexec found here
See the documentation for more information dtexec Utility (SSIS Tool)
Then it's just a matter of installing the correct drivers (32 or 64 bit)