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  Description: SSIS > Error Code > DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Can anyone please suggest what is required to get SSIS working again!!
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.