SSIS incorrect connection string format (intermittently)

Edgars Francis 1 Reputation point
2022-11-23T19:10:08.22+00:00

I have migrated SSIS project from SQL server 2017 to SQL Server 2019. In this project connection strings are loaded dynamically from database - there are always 8 databases through which it iterates. After migration to SQL 2019 I periodically receive this error message - "Format of the initialization string does not conform the the OLE DB specification". Does anyone have experienced something similar? Please take into account that it periodically works and periodically fails regardless of which connection string it is. I am able to run everything withpout errors and sometimes it stops with this error. All connection strings are in the following format:
Data Source=XXXXXDB01;User ID=username;Password=psw;Initial Catalog=db;Application Name=MyApp;Provider=SQLNCLI11.1;

There is already question about the same issue however there is no clear solution. https://learn.microsoft.com/en-us/answers/questions/662740/ssis-project-connection-manager-connection-string.html?childToView=1102134#comment-1102134

Entire error message: DTF Synchronize entities: SSIS Error Code
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNE
CTIONMANAGER. The AcquireConnection method call to
the connection manager "xxxx" failed with error code
0xC0202009. There may be error messages posted
before this with more information on why the
AcquireConnection method call failed.

LoadDimensions:Error: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E73.
An OLE DB record is available. Source: "Microsoft OLE
DB Service Components" Hresult: 0x80040E73
Description: "Format of the initialization string does not
conform to the OLE DB specification.".

Error occurs when: package has been run on production server, SSIS run-time server.

SQL Server version: Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Visual Studio version: Microsoft Visual Studio Professional 2019 Version 16.5.4

SQL Server Integration Services Projects extension for VS2019, its version: 16.0.948.0

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,456 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,026 Reputation points
    2022-11-23T21:55:55.79+00:00

    Hi @Edgars Francis ,

    It is not clear what SSIS Task you are using. Please clarify.

    It is highly recommended to switch to the MSOLEDBSQL, Microsoft OLE DB Driver for SQL Server.
    Any other driver is deprecated.

    You need to take care of software versions:

    1. Update VS2019 up to version 16.11.*
    2. Uninstall SQL Server Integration Services Projects v.4.3, and install v.3.16
      Any 4. version of it is experimental, buggy, and missing some functionality. v.3.16 Download
    3. Upgrade SQL Server 2019 on the server up to Cumulative Update 18. CU18 Download
    0 comments
  2. Kevin Porter 0 Reputation points
    2023-02-07T12:08:54.9633333+00:00

    Encountering the same issue required an upgrade past CU9 when this was fixed as belowimage

    0 comments
  3. Edgars Francis 1 Reputation point
    2023-02-07T13:37:54.2+00:00

    In my case what helped was simply to change the provider from "SQLNCLI11.1" to "MSOLEDBSQL"