question

xnl28 avatar image
xnl28 asked

Microsoft Access Database Engine 2010 on SQL Server 2008 R2

Hello, We have some SSIS packages which are scheduled via SQL Agent. These packages use Excel Connection Manager connections as data sources for spreadsheets. To trial using OPENROWSET to read the spreadsheets directly via T-SQL, we installed the Microsoft Access Database Engine 2010 64-bit on the server. We had to use the /passive option because the 32-bit version was already on the server. After doing this, the existing packages failed when running them from SQL Agent. We had not changed the packages or the jobs. One of the errors given in the SQL Agent job step output was > The step did not generate any output. > The return value was unknown. The > process exit code was -1066598274. The > step failed. So we decided to uninstall the 64-bit version. When trying this, it did so, but requested that SQL Server and SSIS services be restarted. We restarted them. But now, the packages are still failing, but with a different error message: > SSIS Error Code > DTS_E_OLEDB_NOPROVIDER_ERROR. The > requested OLE DB provider > Microsoft.ACE.OLEDB.12.0 is not > registered. Error code: 0x00000000. > End Error Error: 2014-11-04 > 08:00:06.17 Code: 0xC020801C > Source: Excel Source > source [1270] Description: SSIS > Error Code > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Can anyone please suggest what is required to get SSIS working again!!
sql-server-2008-r2ssis excelmicrosoft-access-database-engine
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

raadee avatar image
raadee answered
[HOW TO: FIX ERROR - "the 'microsoft.ace.oledb.12.0][1] [1]: https://social.msdn.microsoft.com/Forums/en-US/1d5c04c7-157f-4955-a14b-41d912d50a64/how-to-fix-error-the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine Check out the comments section for a compatibility list regarding OS.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

xnl28 avatar image
xnl28 answered
We have now resolved the issue. We had to perform a repair installation of "Microsoft Access Database Engine 2010 32-bit" and then reboot the server. After the repair installation and before rebooting, the server did not give any notification that a reboot or restart of SQL Server services was required. We continued getting the same error when executing the package from SQL Agent. But things started working after a server reboot.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.