SSIS Project Connection Manager - Connection string format incorrect

Jens Scho 1 Reputation point
2021-12-14T08:35:33.497+00:00

We have the same Problem as this Ticket.

We have an SSIS project that uses a Project Connection Manager for the destination tables.
It's feeded from a project parameter that contains the connection string:

Data Source=XXX;Initial Catalog=XXX;Provider=MSOLEDBSQL;Integrated Security=SSPI;Auto Translate=False;

When we run the SSIS project on SQL Server 2019 we sometimes, randomly, receive the following error:

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.".

In the SSIS event messages context (catalog.event_message_context) we see that the connection string is sometimes cut off after the provider:

Data Source=XXX;Initial Catalog=XXX;Provider=

This error occurs randomly; sometimes the SSIS project runs succesful, sometimes we receive this error.

Any idea on the root cause? Is this a bug?

Thank you,

We have the same Problem as this Ticket.

https://social.msdn.microsoft.com/Forums/azure/en-US/6eace52b-32c0-4686-9663-1e0d42d253c5/ssis-project-connection-manager-connection-string-format-incorrect?forum=sqlintegrationservices

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

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 34,756 Reputation points
    2021-12-14T09:53:53.337+00:00

    Hi @Jens Scho ,

    May I know your destination source? Is it also SQL Server or any other database?

    If it is SQL Sever, could you please change the provider with SQLNCLI11 for a try?

    Have you check the value of the parameter to make sure the format is correct?

    Here is an article you may take a reference: parameterizing-database-connection-sql-server-integration-services

    The Connection Strings Reference to check the connection string.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments
  2. Jens Scho 1 Reputation point
    2021-12-14T11:20:37.857+00:00

    Hi @ZoeHui-MSFT ,

    thanks for your quick response. The destination is also a SQL Server. And it is the same server where the SQL Agent Job is running.

    157420-image.png

    The Connection is right because mostly the Job run fine. And in the Job we query other databases and put these Data with the same connection in the Datawarehouse. But the error occurs at different companies.

    0 comments
  3. Yitzhak Khabinsky 25,721 Reputation points
    2021-12-14T13:35:45.53+00:00

    Hi @Jens Scho ,

    (1) Your initial connection string is using the best provider. It is recommended by Microsoft since 2018, i.e. MSOLEDBSQL, Microsoft OLE DB Driver for SQL Server.

    I would suggest installing its latest version 18.6: download-oledb-driver-for-sql-server

    (2) Other drivers are deprecated, i.e. SQLNCLI11, and the like.
    You can read about it here: oledb-driver-for-sql-server

    (3) What is the SQL Server version on the SSIS run-time server? Please issue the following command: 

    SELECT @@VERSION;

    and share the result.

    0 comments
  4. Jens Scho 1 Reputation point
    2021-12-14T15:31:50.047+00:00

    Hi @Yitzhak Khabinsky

    The Query Result is 157532-image.png

    And the driver we use in the SSIS Projecht is:

    157459-image.png

  5. Edgars Francis 11 Reputation points
    2022-11-23T15:31:59.92+00:00

    I have the same issue. Did you find solution for it?