Good day, We have many SSIS packages developed and stored in the file system. We're running them using dtexec utility like this dtexec.exe /FILE E:\SSIS_ROOT\VA\package\exec_usp_File_Extract_Setup.dtsx /SET "\package.variables[User::varJobNbr].Properties[Value]";85 /SET "\Package.Variables[User::varProcessTxt].Properties[Value]";File_Extract_MAIN /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING V The packages use connection manager with the connection string like this: <ConfiguredValue>Data Source=servername;Initial Catalog=DB_NAME;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS;Use Encryption for Data=True;Trust Server Certificate=True;</ConfiguredValue> When we try to run our jobs we're getting the following error: The requested OLE DB provider SQLNCLI11.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Our DBA did install 64 bit version of SQL Native Client 11. This is brand new SQL Server 2022 and we only have SSIS installed there, the databases are on different 2019 SQL Server. What should we adjust to make our jobs run? Thanks in advance.

HI @ Naomi Nosonovsky , All Microsoft SQL Server OLEDB Providers are deprecated except Microsoft OLEDB Driver (MSOLEDBSQL) since 2018. I would suggest to download and start using its 64-bit 18.7.4 version here: https://learn.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver16#1874 Obviously, you would need to adjust SSIS connection string. The best way to compose it is via a *.udl file. It will help you to test connection too. After that you can open that udl file in notepad to get a properly formatted and working connection string. A connection string will look similar to connection below: Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog="yourDatabaseName";Data Source=YourSQLServerInstanceName;Trust Server Certificate=True; Please see below.

Hi @ Naomi Nosonovsky , Edit the job step. Go to "Execution options". Try "Use 32 bit runtime" option. Regards， Zoe Hui If the answer is helpful, please click "Accept Answer" and upvote it.

SSIS server 2022 connecting to SQL Server 2019 - Jobs are failing

Accepted answer

Yitzhak Khabinsky 25,961 Reputation points

2024-11-15T20:46:54.6533333+00:00

HI @Naomi Nosonovsky,

All Microsoft SQL Server OLEDB Providers are deprecated except Microsoft OLEDB Driver (MSOLEDBSQL) since 2018.

I would suggest to download and start using its 64-bit 18.7.4 version here: https://learn.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver16#1874

Obviously, you would need to adjust SSIS connection string. The best way to compose it is via a *.udl file. It will help you to test connection too.

After that you can open that udl file in notepad to get a properly formatted and working connection string.

A connection string will look similar to connection below:

Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog="yourDatabaseName";Data Source=YourSQLServerInstanceName;Trust Server Certificate=True;

Please see below.
Please sign in to rate this answer.
Naomi Nosonovsky 7,886 Reputation points

2024-11-18T13:32:35.2066667+00:00

Hi Yitzhak, right now I don't have access to the OS of this new SSIS server :( Do you mean I would need to RDP to the server and create udl there once we have the driver installed (I would need to ask our DBA to install it). Also, does it matter that we use dtexec to run our packages?

Naomi Nosonovsky 7,886 Reputation points

2024-11-18T13:44:31.72+00:00

One more question - the most recent version is 19.3.5 I assume we should go with this, right?

Yitzhak Khabinsky 25,961 Reputation points

2024-11-18T13:52:19.3733333+00:00

Hi Naomi,

Yes, you would need to RDP to connect to the SSIS run-time server, and install there the proper driver, deal with a udl file, etc.

It is preferrable to use SSIS Catalog (circa SSIS 2012 onwards), SSISDB, etc. with their endless benefits in comparison with keeping SSIS packages on the file system (circa SSIS 2005 - 2008R2).

Though the DTEXEC way is still working.

Yitzhak Khabinsky 25,961 Reputation points

2024-11-18T13:55:45.21+00:00

One more question - the most recent version is 19.3.5 I assume we should go with this, right?

The 18.7.4 driver version is easier to deal with. It should work for you.
IMHO, the 19.* driver added new functionality targeting Azure .

Erland Sommarskog 112.8K Reputation points MVP

2024-11-18T20:31:50.62+00:00

IMHO, the 19.* driver added new functionality targeting Azure .

That functionality is also in MSOLEDBSQL, version 18. But an important difference is that MSOLEDBSQL19 defaults to requiring encryption by default, like all modern APIs from Microsoft do. Certainly a good thing, but requires some work to get going.

Naomi Nosonovsky 7,886 Reputation points

2024-11-20T14:44:55.58+00:00

I tried the following connection string:

Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=VA_REPOSITORY;Data Source=SERVERNAME;Initial File Name="";Use Encryption for Data=True;Trust Server Certificate=True;Server SPN="";Authentication="";Access Token=""

I removed the parts from it which had "" in them and put into CONFIG file for C# application. I got Program ending with error: Keyword not supported: 'provider'..

Looking at this thread https://stackoverflow.com/questions/27387912/keyword-not-supported-provider-opening-sqlconnection suggests to either remove provider from the config or change the application to use OleDbConnection instead of SqlConnection.

Is it really the route we need to take - change the application?

Yitzhak Khabinsky 25,961 Reputation points

2024-11-20T17:02:10.6366667+00:00

Naomi,

No need to use any c# app.

The udl connection should work in SSIS.

I removed the parts from it which had "" in them

That's correct. You can delete such parts.

Erland Sommarskog 112.8K Reputation points MVP

2024-11-20T21:55:49.25+00:00

The Provider keyword serves to specify which OLE DB provider you want to use. That is, it presumes that you are using OLE DB. But you say SqlConnection which is System.Data.SqlClient or Microsoft.Data.Client and which has nothing to do with OLE DB.

Overall, when connecting to SQL Server, you need to have an understanding which API you are using. While all APIs largely support the same features, there are smaller differences in the exact wording of the connection-string keywords, so you need to look in the correct manual.

Naomi Nosonovsky 7,886 Reputation points

2024-11-20T22:44:12.5566667+00:00

Looks like we got everything working or nearly working. There was an account our DBA needed to set up. I changed provider where it was explicitly set to MSOLEDBSQL.1 and no other changes in connection string and all is working.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSIS server 2022 connecting to SQL Server 2019 - Jobs are failing

1 additional answer

Your answer