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

Naomi Nosonovsky 7,971 Reputation points
2024-11-15T18:33:44.1866667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,165 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,615 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,201 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.

    UDL_11-15-2024_01


1 additional answer

Sort by: Most helpful
  1. ZoeHui-MSFT 38,626 Reputation points
    2024-11-18T06:09:35.8533333+00:00

    Hi @Naomi Nosonovsky,

    Edit the job step. Go to "Execution options". Try "Use 32 bit runtime" option.

    User's image Regards,

    Zoe Hui


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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.