SSIS MSOLEDBSQL: ConnectRetryCount, ConnectRetryInterval

Question

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?

Answer 1

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