SSIS connection issues

Question

Hi everybody,

We upgraded our SQL Server to SQL Server 2022. The SSIS server is 2019. We're getting the following error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

We tried various connection strings, the most recent is

Data Source=ListenerName;Driver={ODBC Driver 18 for SQL Server};Initial Catalog=VA_REPOSITORY;Integrated Security=SSPI;Encrypt=yes;TrustServerCertificate=yes;MultiSubnetFailover=yes

The OleDB errors are still the same. Do you know what can we try to fix them? I can connect to database fine from SSMS.

Also, our packages are running from SQL Server jobs using dtexec utility. The packages mostly target SQL Server 2016 version, but one package I changed today to target 2019.

Thanks in advance.

Answer 1

Hi @Naomi Nosonovsky,

It is better to stay with Micrsoft OLEDB Driver instead of ODBC driver.

To connect to SQL Server 2022 from SSIS, the OLEDB v.18 string need the following structure:

Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2022;Data Source=DESKTOP-MRK99IF;Use Encryption for Data=True;Trust Server Certificate=True;

The most important are the following mandatory parameters for SQL Server 2022:

• Integrated Security=SSPI
• Use Encryption for Data=True
• Trust Server Certificate=True