Share via

Where is this option???????

Naomi 7,361 Reputation points
2020-12-01T19:41:14.29+00:00

Hi everybody,

My colleague is trying to create a SSIS package to use Excel as data source and when we try to execute it using DTS utility we're getting this error

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. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Googling this error suggests to set 64Bit runmode to false. I do remember I was able to find it somewhere before but I search and search in various windows in VS 2019 and I am unable to find this option. This is what StackOverflow thread says

In Visual Studio 2019, the option "64BitRuntime" is in the Solution Properties dialog, under debugging.

I open just the dtsx in VS 2019 and I don't see such option anywhere... :( Can you please clarify what should we do (and changing file format to CSV is also not an option for us).

Thanks in advance.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,479 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,286 Reputation points
    2020-12-01T20:19:10.757+00:00
    • It is better not to use Microsoft.Jet.OLEDB.4.0. It is very old and comes just as a 32-bit version.
    • Microsoft ACE OLEDB provider is its replacement.
      It comes in both 32-bit and 64-bit versions.

    You need to modify your SSIS package to start using ACE provider.

    • Dev. environment with VS2019 would need ACE 32-bit installed. It is VS2019 limitation.
    • Server run-time environment needs ACE 64-bit installed.

    To check if 64-bit ACE OLEDB provider is installed on the server, run the following statement in SSMS: 

    EXEC master.sys.sp_MSset_oledb_prop;

    It should look along the following screen shot, though the ACE version could be different:

    44205-microsoftaceoledb120.png

    1 person found this answer helpful.
    0 comments
  2. Naomi 7,361 Reputation points
    2020-12-01T20:55:18.377+00:00

    One related question - will dtsexec utility work fine with that provider? And the other question - do we need to install it on the server if it is not installed already?

  3. Naomi 7,361 Reputation points
    2020-12-01T21:47:00.257+00:00

    Ok, we tried this provider and now we're getting similar error:

    Microsoft.ACE.OLEDB.16.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
    An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

    What should we do about it?

  4. Monalv-MSFT 5,896 Reputation points
    2020-12-02T07:16:20.56+00:00

    Hi @Naomi ,

    Please download Microsoft Access Database Engine.

    Hope the following links will be helpful:

    1.Microsoft Access Database Engine 2016 Redistributable

    2.Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

    44130-ace.png

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?