0

On our dev environment we have MS Office 365 32-bit version on Windows 11 64-bit and Visual Studio 2022 with SSDT.

The issue we are experiencing is in SSIS when creating Excel Destination in order to write data into an excel file. When trying to map the columns from source to Excel Destination we get an error message below: "The requested ole db provider microsoft.ace.oledb.12.0 is not registered if the 64 bit driver is not installed, run the package in 32-bit mode. error code: 0x00000000"

I know Visual Studio 2022 is 64-bit and the debug option Run64BitRuntime is greyed out and it is set to True.

The database server, where the SSIS package has to run on (as SSIS package file through SQL Server job), the Office and the Excel app in 64-bit with Microsoft Access Database Engine installed. Not sure if that is 64 or 32 bit version.

Do you know, if we need 32-bit or 64-bit version Microsoft Access Database Engine on dev machine? And how about on Database Server, where package is run?

We probably need to install 32-bit version Microsoft Access Database Engine 2016, as we have Office 365 32-bit version on our dev machines. Right? If we install 32-bit Microsoft Access Database Engine 2016 on our dev machine, will this solve the issue with error above? And can we run the package as 64 bit on database server?

Thanks in advance for your help.

Kind Regards

Improve this question
1
  • Another observation I had recently is that I in SSMS Import and Export Wizard I have Microsoft Excel in the drop down list for the Data Source or Destination. But, in Visual Studio SSIS the Microsoft Excel is missing in the drop down list for the SSIS Import and Export Wizard! And I assume, SSMS is 32 bit application. Commented Feb 18 at 20:50

1 Answer 1

Reset to default
1

It sounds like you're dealing with a common issue when working with SSIS and Excel files, especially when there's a mix of 32-bit and 64-bit components. Let me break this down for you:

  1. Since you have Office 365 32-bit installed on your dev machine, you’ll need the 32-bit version of the Microsoft Access Database Engine. This is because SSIS needs to match the bitness of the Office installation to interact with Excel files properly.
  2. Installing the 32-bit Access Database Engine should resolve the error you're seeing during column mapping in Visual Studio.
  3. On the database server where the SSIS package will run, if the Office installation is 64-bit, you’ll need the 64-bit version of the Microsoft Access Database Engine. This ensures compatibility when the package is executed via SQL Server Agent.
  4. If the server has a 64-bit Office installation, the package should run in 64-bit mode without issues.
  5. Dev Machine: Install the 32-bit Access Database Engine to match your 32-bit Office installation.
  6. Database Server: Install the 64-bit Access Database Engine if the the server has a 64-bit Office installation.
  7. Package Execution: Ensure the Run64BitRuntime setting is correctly configured for development and deployment. On the server, it should be set to True if running in 64-bit mode.
  8. If you’re developing in Visual Studio 2022 (64-bit) but need to debug in 32-bit mode, you might need to adjust your project settings or use a 32-bit version of Visual Studio for debugging purposes.
  9. Double-check the bitness of the Access Database Engine installed on both the dev machine and the server to avoid mismatches.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks Abu Rahat Sabir, it sounds reasonable. The only issue is that SSIS packages are then implemented on Visual studio 2022, and it is only 64 bot version of it available. Also, the debug option Run64BitRuntime in Visual Studio 2022 SSDT, as mentioned, is greyed out and it's set to true and ypu can't change it. Not sure how to debug on dev machine. Or would I be able to run the package even when MS Access database engine is 32 bit installed?

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.