question

Woody-6707 avatar image
0 Votes"
Woody-6707 asked ZoeHui-MSFT commented

Using MSOLEDBSQL on Azure SSIS Integration runtime

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




azure-data-factorysql-server-integration-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Woody-6707,

You may try to set the Run 64bitRunTime to false to see if it is helpful.

91633-screenshot-2021-04-27-154500.jpg

installing-oledb-driver-for-sql-server

SQL Server Native Client (SQLNCLI11.1) is deprecated and not installed by VS2019.

So it is recommend upgrading to the new Microsoft OLE DB driver for SQL Server.

https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects
Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




0 Votes 0 ·

Hi Zoe,
Thanks for your response but as I mentioned in my first post, I have other connections in these packages that require it to run as 32 BIT at the moment.
I also mentioned that SQLNCLI11.1 is deprecated and that I am trying to install the OLEDB drivers.


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?

0 Votes 0 ·

Hi @Woody-6707,

Just checking to see if the below suggestion from @swinarko was helpful. Incase if that doesn't help as suggested please do file a support ticket for deeper investigation.
In case if you don't have a support plan, do let us know.

Thank you

0 Votes 0 ·

Hi @Woody-6707,

We still have not heard back from you. Just wanted to check if the below suggestion from @swinarko was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

swinarko-MSFT avatar image
1 Vote"
swinarko-MSFT answered

The 64-bit version of MSOLEDBSQL driver has been preinstalled on SSIS IR, see https://docs.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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @swinarko-MSFT . This was the answer.

0 Votes 0 ·