August 13, 2024 at 2:21 pm
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
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:
I receive the following error: Failed to configure a connection property that has the following path:
Has anyone been able to get these settings to work?
August 13, 2024 at 2:39 pm
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
Hi Phil,
I think these are the settings:
August 14, 2024 at 2:18 pm
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
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:
Thanks for your help.
August 14, 2024 at 2:47 pm
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