The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered

After a SQL Server migration & upgrade to a new host with SQL Server 2022, one of the many issues I needed to resolve was a SQL Agent job that was failing. It consisted of a single job step that ran an SSIS package from a UNC path. The package itself is very simplistic: it truncates a SQL Server database table and "loads" it with data from a table in an Access database file. Here is the truncated error message from the SQL Agent log:

I had a pretty good idea what the problem was: a driver that had previously been installed on the old host was missing on the new SQL host . Since the old host was still available, I checked the available providers via sp_enum_oledb_providers. (I couldn't find a support document for this procedure from Microsoft. Perhaps it's an undocumented sproc?) Much to my surprise, the "Jet" OLEDB provider was nowhere to be found.

I did see Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.16.0 providers. I knew those were both a result of installing the Microsoft Access Runtime 2016. It made sense that the SSIS package should be able to use one of those--OPENROWSET can use those to read both Excel spreadsheet files and Access database files. But from my development workstation, I didn't see either of the "ACE" OLEDB providers.

Now what? Could I install the Microsoft Access Runtime 2016 on my workstation? If so, that should give me both of the "ACE" OLEDB providers that are available on the SQL Server host. I tried the 64-bit version of the installation first, but it would not install. From the error message, it looks like the Runtime doesn't play well with an existing installation of MS Office. That makes some sense.

I also tried the 32-bit version of the installation. I was not hopeful. It also failed, and I'm not surprised. As noted above, my workstation already had an existing installation of MS Office...the 64-bit version to be precise.

Perhaps I could manipulate a connection string, and change it from "Jet" to "ACE". Here's what I was seeing in Visual Studio.

I changed the connection string from this: Data Source=\\RemoteServer\SSIS_Projects\MyProject\MyAccessDB.mdb;Provider=Microsoft.Jet.OLEDB.4.0;

...to this: Data Source=\\RemoteServer\SSIS_Projects\MyProject\MyAccessDB.mdb;Provider=Microsoft.ACE.OLEDB.16.0;

Visual Studio immediately let me know there was an issue. I fully expected this. Here is what the connection looked like before the connection string change...

...and here is what the connection looked like after the connection string change.

The new error message in the IDE said

That error can be addressed by unchecking "Work Offline".

That fixed one error message, but revealed another:

Still, I had a feeling that in spite of the latest error, the SSIS package might still run from the server. So I deployed it. And it worked!