- Microsoft Native Client(s) are deprecated since 2018. Microsoft OLEDB Driver is their replacement. So, you made a correct choice.
- SSIS Configure on the server shows most common connection properties.
But you can add/modify your two settings in the ConnectionString setting value directly. That's the final connection that is used for execution. - These settings are visible and editable in the Visual Studio SSIS Connection Manager. Please see below.
SSIS MSOLEDBSQL: ConnectRetryCount, ConnectRetryInterval
Using SSISDB on server A, we have a parent package that loops through a configuration table and executes several hundred stored procedures on server B. Occasionally one of the processes fails with
- Failed to acquire connection "<database>". Connection may not be configured correctly, or you may not have the right permissions on this connection.
This is typically in the middle of the execution, so a connection has been made in previous steps. We are currently using an OLEDB Connection (Native Client) for these connections. I have proposed switching the connections to MSOLEDBSQL to use the Connection Resiliency features (Connect Retry Count, Connect Retry Interval). When testing locally, I disconnect from our VPN and I can see the package (under the Progress tab) attempting to reconnect at the intervals specified in the Connection Manager>Properties>ConnectRetryCount & ConnectRetryInterval (which override the ConnectionString values of Connect Retry Count=xxx; Connect Retry Interval=xxx;).
When deploying to the SSISDB and trying to set these values in the SSISDB under Configure... for the Project Connection Managers:
- ConnectRetryInterval
- ConnectRetryCount
I receive the following error: Failed to configure a connection property that has the following path:
- Message: \Packages.Connections...Properties[ConnectRetryInterval]. Element "ConnectRetryInterval" does not exist in collection "Properties".
- Message Source Name: Transact-SQL stored procedure
Has anyone been able to get these settings to work?Using SSISDB on server A, we have a parent package that loops through a configuration table and executes several hundred stored procedures on server B. Occasionally one of the processes fails with
- Failed to acquire connection "<database>". Connection may not be configured correctly, or you may not have the right permissions on this connection.
This is typically in the middle of the execution, so a connection has been made in previous steps. We are currently using an OLEDB Connection (Native Client) for these connections. I have proposed switching the connections to MSOLEDBSQL to use the Connection Resiliency features (Connect Retry Count, Connect Retry Interval). When testing locally, I disconnect from our VPN and I can see the package (under the Progress tab) attempting to reconnect at the intervals specified in the Connection Manager>Properties>ConnectRetryCount & ConnectRetryInterval (which override the ConnectionString values of Connect Retry Count=xxx; Connect Retry Interval=xxx;).
When deploying to the SSISDB and trying to set these values in the SSISDB under Configure... for the Project Connection Managers:
- ConnectRetryInterval
- ConnectRetryCount
I receive the following error: Failed to configure a connection property that has the following path:
- Message: \Packages.Connections...Properties[ConnectRetryInterval]. Element "ConnectRetryInterval" does not exist in collection "Properties".
- Message Source Name: Transact-SQL stored procedure
Has anyone been able to get these settings to work?
1 answer
Sort by: Most helpful
-
Yitzhak Khabinsky 25,866 Reputation points
2024-08-13T17:35:12.8466667+00:00