Summary: A "master" database houses a set of connection strings. A .dtsConfig XML file is used to point the packages to this database. Dynamic connection assignment accomplished by using package variables and expressions on the connections. Works flawlessly in the development environment, but once deployed to live it falls over.
I'm currently running into issues when deploying .dtsx packages to a production environment. The issue is specifically related to the connection manager when the jobs run. The history log reports a ... network ... error with a Login timeout error as the reason.
(For reference, I'm using Visual Studio 2013 with SQL Server Data Tools)
I have a table in a master database that holds the connection strings that it needs to process.
The packages check for a configuration file, that points them to the database as stipulated in (1).
The connections are retrieved and are placed in an object variable.
The variable is mapped to a foreach loop container, where a set of connection string variables are mapped to the relevant columns.
The packages then progress as normal.
Some notes:
When I did the development, I provided default values on my network for the connection strings.
I have checked the connection string parameters and formatting inside the database, and they conform to Microsoft's specification.
Our implementers installed SSDT on a client's QA server, where I altered the connection variables to point to their network. This solved the problem, but it is not sustainable (in my mind at least).
So my question is: how do I get my production deployments to work correctly with dynamic connection management assignment without having to alter the connection string variables inside each package on a per client basis?
Any help will be appreciated.
After doing more research by posing plenty of questions to DuckDuckGo, I finally got my answer here.
A quote from the above blog:
I had a set of SSIS packages running for my client using the third option listed above. The packages worked fine for ages until one fine day when they failed. The logs showed the packages had failed validation and I discovered that all the packages had their connection managers’ DelayValidation property set to False. The variable used to set the connection string had a default value pointing to the DEV server. These packages in production were actually trying to validate against DEV database though the connection string was dynamically set via a variable to point to PROD. This was dangerous as the jobs will not run if DEV server was down, which is exactly what had occurred.