Using MSOLEDBSQL on Azure SSIS Integration runtime

Question

Hello,
We are in the process of moving our SSIS packages from on-prem into Azure. We have setup an SSIS integration runtime and the packages are running successfully. All our existing packages use the SQLNCLI11.1 (SQL Server Native Client) to connect to the data. Since this is being deprecated I thought it would be a good opportunity to change the provider to MSOLEDBSQL.1 (Microsoft OLE DB Driver for SQL Server). After redeploying and attempting to run I get the following error:

The requested OLE DB provider MSOLEDBSQL.1 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode.

Now, when starting up the integration runtime there is a script called main.cmd that gets called. Within this script I attempt to install the MSOLEDBSQL drivers:

msiexec /i msoledbsql-86.msi /qn /lv install-msoledbsql-86.log ACCEPTMSOLEDBSQLLICENSETERMS=YES
msiexec /i msoledbsql-64.msi /qn /lv install-msoledbsql-64.log ACCEPTMSOLEDBSQLLICENSETERMS=YES

The drivers were downloaded from Download Microsoft OLE DB Driver for SQL Server

Now, the 64GB driver seems to have been installed. The following line was in the log file:

MSI (s) (64:A4) [05:04:32:958]: Windows Installer installed the product. Product Name: Microsoft OLE DB Driver for SQL Server. Product Version: 18.5.0.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Installation success or error status: 0.

The 32 bit driver did not install:. The following was in the log file:

MSI (s) (64:04) [05:04:23:372]: Product: Microsoft OLE DB Driver for SQL Server -- Installation of this product failed because it is not supported on this operating system. For information on supported configurations, see the product documentation.

I am running the SSIS package as 32 BIT. I have other connections in these packages that require it to run as 32 BIT at the moment. Why do I need to install MSOLEDBSQL drivers on the integration runtime, but not the deprecated SQLNCLI11.1 driver?
Also, any idea why the 32 BIT driver won't install?
Thanks

Answer 1

The 64-bit version of MSOLEDBSQL driver has been preinstalled on SSIS IR, see https://learn.microsoft.com/azure/data-factory/built-in-preinstalled-components-ssis-integration-runtime#built-in-and-preinstalled-clients-drivers-and-providers-on-azure-ssis-ir.

With many of these drivers, installing their 64-bit version usually also installs their 32-bit version.

Consequently, you can try to remove your custom setup for SSIS IR and simply replace SQLNCLI11.1 w/ MSOLEDBSQL (not MSOLEDBSQL.1) in relevant connection strings.

If that doesn't work, you can try to install just the 32-bit version of MSOLEDBSQL driver via custom setup.

If that also doesn't work, please raise a support ticket and we'll investigate further.