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).
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.