Microsoft.ACE.OLEDB.16.0 upgrade SSIS Unexpected termination

Question

We have SSIS package installed in SSIS catalog server. In the catalog server, Microsoft.ACE.OLEDB.12.0 is installed. The jobs were running fine without issues. We are importing into Excel Template.

Our connection string looks like below:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\UNCPATH\Report_Template.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

Now, they have upgraded the catalog server ACE driver version to Microsoft.ACE.OLEDB.16.0 64 BIT. With the upgrade in catalog server, the packages are failing with "UnExpected Termination". When looking into the messages, it is failing during validation of the Data Flow Task related to excel destination.

The same package is running fine locally in VS and also in local catalog server. In locally also, I have Office 365 64 bit.

Not sure, why it is failing only on the catalog server with Microsoft.ACE.OLEDB.16.0.

• Is this due to installable of Microsoft.ACE.OLEDB.16.0 64 bit ?
• Is office 64 bit having different ACE driver ?

Answer 1

The error is due to different installations of SQL Server and Office installation.

SQL Server is not able to access the Access provider, if the installation of office was Click to Run installation.

The issue can be resolved by any of the following approaches.

OLEDB driver issues resolution

Resolution

Beginning with Microsoft 365 Apps for Enterprise Version 2009, work has been completed to break ACE out of the C2R virtualization bubble so that applications outside of Office are able to locate the ODBC, OLEDB and DAO interfaces provided by the Access Database Engine within the C2R installation.

Use the following table to understand if additional components are necessary to access these intefaces within your environment: