When I develop SSIS packages, I set the connect managers’ connection strings in one of the following ways:
- Package Variables reading from an XML config file
- Environment Variables
- Package Variables whose values are passed from a SQL job invoking the package
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.
Whenever you have a connection string set dynamically using variables, the best practise is to set the DelayValidation property to True for all the connection managers and all the Data Flow Tasks. I found the following blog article to be useful with some quick handy tips.
The No-Brainer Lab 