0

I am using SQL 2019, SSISDB is installed. My customer creates an SSIS package using the import/export wizard to import an excell file, and that was successful only after I had to install the 32bit version of Microsoft Access Database Engine 2016 Redistributable.

However, when he tries to run the package from a SQL Agent job, he gets the following message:

"...Description: The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode...."

Should I be installing the 64-bit version as well on the same server that I already installed the 32-bit version? I am unable to find an option in SSMS to configure the job to run with the 32-bit option.

Kindly advise please.

thank you

1 Answer 1

2

I found this and it worked for me before. Hope it helps

  1. Install the 64-bit Version of Microsoft Access Database Engine:
    If your SQL Server Agent job runs in 64-bit mode, you should install the 64-bit version of Microsoft Access Database Engine 2016 Redistributable on the server where SQL Server is installed. This will allow the job to access Excel files using the 64-bit ACE OLEDB provider. Be sure to download and install the 64-bit version from Microsoft's website.

  2. Configure SQL Server Agent Job Execution Mode:
    In SQL Server Management Studio (SSMS), connect to your SQL Server. Expand the "SQL Server Agent" node in the Object Explorer. Right-click on your SQL Agent job and select "Properties." In the "Job Properties" dialog, go to the "Steps" page. Select the step that runs your SSIS package. Click the "Edit" button. In the "Edit Job Step" dialog, go to the "Execution Options" tab. You will find an option called "Use 32 bit runtime." By default, it is unchecked, which means the job runs in 64-bit mode. If you want to run the job in 32-bit mode, check this option. Click "OK" to save the changes.

  3. Modify Connection Managers in SSIS Package:
    Open your SSIS package in SQL Server Data Tools (SSDT). Check the Excel Connection Manager(s) used in your package. Ensure that they are configured to use the appropriate ACE OLEDB provider based on whether you are running the package in 32-bit or 64-bit mode. For 32-bit mode, use the 32-bit ACE OLEDB provider. For 64-bit mode, use the 64-bit ACE OLEDB provider.

  4. Save and Deploy the Modified Package:
    After making the necessary changes to your SSIS package, save it and deploy it to the SSISDB on your SQL Server.

  5. Reconfigure the SQL Agent Job:
    If you changed the execution mode of the SQL Agent job, go back to the job properties and ensure it is set to run in the mode you want (32-bit or 64-bit) based on the ACE OLEDB provider you have installed and configured.

By following these steps, you should be able to configure your SQL Server Agent job and SSIS package to use the correct ACE OLEDB provider based on the execution mode, whether it's 32-bit or 64-bit. This should resolve the error you encountered.

4
  • apologies for my delayed response, I was off work yesterday. Thank you so much for being so thorough in your answer. The problem I have here is that in your step 2 above, my SQL Agent Execution Options page does not give me the choice of a 32 or 64 bit. My SQL is version 2019 and SSMS is 18.5. Could it be, from what you are saying, that my Agent is probably running in 64-bit mode by default? SSMS is a 32bit application, so I would have thought 32bit will be the default for SQL Agent? However, do you know if I can install 64bit in addition to the already installed 32bit of the driver
    – PTL_SQL
    Sep 13, 2023 at 11:27
  • if both 64 and 32 bits of the Microsoft Access Database Engine 2016 Redistributable can co-exist without any issue, I am happy to go ahead and install the 64bit in addition to the already installed 32bit. I'd appreciate your advise please. Thank you.
    – PTL_SQL
    Sep 13, 2023 at 11:31
  • It's common to have both versions of the ACE OLEDB provider installed to accommodate different scenarios. I've used it this way before and didn't encounter any problems but still remember that the choice between 32-bit and 64-bit mode depends on the bitness of your SQL Server, the ACE OLEDB provider, and the specific requirements of your SSIS package.
    – mbielsa
    Sep 14, 2023 at 7:27
  • thank you so much, I have additionally installed the 64bit version successfully, and all is working well now. I appreciate your time and efforts and for providing the correct solution.
    – PTL_SQL
    Sep 18, 2023 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.