SSIS Dynamic Connection String – DelayValidation

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.

http://www.louisnguyen.info/Home/ssis-best-practices

Advertisements

One thought on “SSIS Dynamic Connection String – DelayValidation

  1. Thank you so much. I have been struggling with this for days. The bit I was missing was the “set the DelayValidation property to True for all the connection managers AND ALL THE DATA FLOW TASKS”. I had, stupidly as it turned out, assumed that setting DelayValidation to true for the connection manager would be enough since that was all I was dynamically altering!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s