0

I am able to run an SSIS package from my machine without issue in Visual Studio 2017, but I am unable to run the same SSIS package on the same machine in Visual Studio 2022. I am running Windows 10 Enterprise 64 bit.

The error message when trying to run the SSIS package in VS 2022 is:

Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine.

The part of the SSIS package that fails is in a C# Script Task that is trying to open a connection to an excel file using the following code:

string ConnectionString;
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::ExlFQFileName"].Value.ToString() +
                ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnectionString);

I am not sure why the same SSIS package being run on the same local machine works when run in VS 2017 but not when run in VS 2022.

I've tried installing the "missing" provider even though VS 2017 is apparently able see it and use it, but VS 2022 thinks it's missing (or can't see it?).

I believe we have Office (Microsoft) 365 installed on our machines.

My excel version is: Microsoft® Excel® for Microsoft 365 MSO (Version 2209 Build 16.0.15629.20256) 64-bit

How can I resolve the error in VS 2022?

Update: Using the link provided by billinkc in the comments, I was able to determine that VS 2022 is running the Script Task in 64 bit mode (where it doesn't work) and VS 2017 is running the Script Task in 32 bit mode (where it works fine).

https://billfellows.blogspot.com/2015/02/is-my-ssis-package-running-in-32-or-64.html

I've tried setting VS 2022 Run64BitRunTime to False but it still runs the script task in 64 bit mode.

How can I force VS 2022 to run the script task in 32 bit mode like it does in VS 2017?

Or should I try to update the script task to use: Microsoft.ACE.OLEDB.16.0 instead of Microsoft.ACE.OLEDB.12.0?

Ultimately, I am just looking to get the package to run in VS 2022 like it does in VS 2017 regardless of the bit version it uses.

Improve this question
2
  • 1
    My strong suspicion is that the 2022 version of the SSIS project is running in 32 bit mode. I'd drop a Script Task into the 2017 and 2022 package and run it to confirm both are operating in the same bit-ed-ness billfellows.blogspot.com/2015/02/…
    – billinkc
    Dec 20, 2022 at 14:33
  • @billinkc Thank you for the link to the article. I feel I am now a step closer to getting the issue resolved. I have updated the question.
    – Juan Velez
    Dec 20, 2022 at 19:36

0

Reset to default

Your Answer

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