SSIS Project Connection Manager - Connection string format incorrect

Question

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

Answer 1

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.

Answer 2

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.

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.

Answer 3

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.

Answer 4

Hi @Yitzhak Khabinsky

The Query Result is

And the driver we use in the SSIS Projecht is:

Answer 5

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