Post reply

SSIS MSOLEDBSQL: ConnectRetryCount, ConnectRetryInterval

  • August 13, 2024 at 2:21 pm

    #4444534

    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?

    Attachments:
    You must be logged in to view attached files.

  • August 13, 2024 at 2:39 pm

    #4444539

    Do you have the same version of SQL Server on your machine and on the server?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 14, 2024 at 1:38 pm

    #4444889

    Hi Phil,

    I think these are the settings:

    • local>Programs and Features>Microsoft SQL Server Integration Services Projects: 15.0.2000.180
    • server>Microsoft SQL Server 2019>SQL Server 2019 Configuration Manager>SQL Server Services>SQL Server Integration Services 15.0

  • August 14, 2024 at 2:18 pm

    #4444906

    Not quite what I asked, but good enough!

    I'm wondering why the source of that message is 'Transact SQL Stored Procedure' and not from the connection manager, but I'm not sure I can help you further with this one, as it's not something I've ever tried to configure like that. Why not simply configure them on the CM and then deploy to SSISDB?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 14, 2024 at 2:29 pm

    #4444907

    Hi Phil,

    I think you are saying configure in the SSDT Project how I want and then deploy to server. I have that set as my current setting. I think the two concerns I have are:

    • I am not sure if it is using those values that are deployed
    • This is in a shared repo, typically on deployment we override the values in the repo with environment variables, so if they are changed in the repo for testing (either on purpose or accident) it doesn't impact the server executions.

    Thanks for your help.

  • August 14, 2024 at 2:47 pm

    #4444915

    That is what I was suggesting. I also use SSISDB environment variables to override environment-specific items. But if the settings are the same regardless of environment (eg, the name of stored procedure to execute at stage n, which is usually the same, regardless of dev, QA or Prod environment) then I do not use a variable to configure that … it would be unnecessary.

    I was thinking that the same would be true of these connection settings – that they would be the same across environments. And that you would actually want them to be the same, for development and testing purposes.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply