Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

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:

Executed as user: domain\redacted. Microsoft (R) SQL Server Execute Package Utility Version 16.0.1000.6 for 64-bit Copyright (C) 2022 Microsoft. All rights reserved. Started: 4:06:30 PM Error: 2023-06-14 16:06:30.79 Code: 0xC0209303 Source: MDB_File_Connection Connection manager "Access_Table_Name" Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.

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.

Dave Mason - SSIS Connection Manager

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.

Dave Mason - Microsoft Access Runtime 2016 x64

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.

Dave Mason - Microsoft Access Runtime 2016 x86

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

Dave Mason - SSIS Connection String

I changed the connection string from this: Data Source=\\RemoteServer\SSIS_Projects\MyProject\MyAccessDB.mdb;Provider=Microsoft.Jet.OLEDB.4.0; 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.

Dave Mason - SSIS OLEDB Source

The new error message in the IDE said

Error at Access_Table_Name: The connection manager "MDB_File_Connection" will not acquire a connection because the connection manager OfflineMode property is TRUE. Whe the OfflineMode is TRUE, connections cannot be acquired.

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

Dave Mason - SSIS Connection, Work Offline

That fixed one error message, but revealed another:

Error at Access_Table_Name [Connection manager "MDB_File_Connection"]: The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode.

Dave Mason - SSIS OLEDB Source

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!


Post comment