Home > Database, SQL Server, SSIS > SSIS and DelayValidation

SSIS and DelayValidation

October 14, 2010

If you’re a regular reader then you’ll know I’ve recently become enamored of SSIS.  I’m absolutely blown away by this product, I wish I learned about it 5 years ago!

Of course, being new to such an extensive technology means dealing with landmines along the way.  Unfortunately, I’ve run into a couple of them already.  The first one was my own fault: I developed a project in 2008 but the client has Sql Server 2005.  To solve it I had to install Sql Server 2005 and create a 2005 version of the Package.

AcquireConnection Method Error

The second issue I’ve encountered was hardly as intuitive.  I was at the client site yesterday deploying my first SSIS Package outside my own network.  With the help of Andy Leonard, my SSIS mentor, I had worked through all the necessary steps to make the Package dynamic, a process worthy of a blog post of its own.  At the client site I followed my cheat sheet to the letter, explaining every step to the client along the way.  After solving a missing prerequisite problem on the server, we fired up the Package only to be quickly stopped with a Validation failure message:

The AcquireConnection method call to the connection manager failed with error code 0xC0202009.

I assumed there was a problem with the dynamic connection strings. I spent about an hour diagnosing the problem only to find out there was no problem, at least not with the dynamic connections.  The problem, it turns out, was in a thing called DelayValidation.

Dynamic vs. Design Time Connection Strings

When you design an SSIS Package, you need to define connections to data sources and destinations that you can access at design time. Without these connections, SSIS would be pretty useless. So these design time data connections have to be hard coded somewhere: I put mine in package variables and bind the ConnectionString property of the ConnectionManager objects to the variables.  These variables are then overridden at runtime by external values, making the connections dynamic and portable.

What I was unaware of was that each ConnectionManager object and each DataFlow Task has a property called DelayValidation.  When the Package begins, it validates all of the DataFlow tasks prior to executing any of them.  If DelayValidation is set to False, then the validation process uses the design time connection objects.  In other words, it tries to connect using that connection string before it has a chance to be set by the dynamic process.  And here’s the kicker: False is the default value, so this is the default behavior.

Solution

Working on my own network, I never even noticed this was happening.  On the client network, however, it immediately blows up because the tasks cannot connect to my design time databases.  I found a good article online that explained the problem and it actually makes sense.  The solution is to set DelayValidation to True, which will delay the validation process until the task begins to execute, giving it time to be set dynamically.

I started by setting the property on every ConnectionManager object. At first I believed that this would be sufficient but it didn’t stop the validation error: I had to do the same thing to every DataFlow task as well, after which the Package ran properly.  This was a real pain because I had so many to change. I could select multiple tasks within the same container, but I could not select tasks across container boundaries, and I have a lot of containers.

While I understand what DelayValidation is doing and why it does it, I don’t understand why False is the default value.  If nothing else, it seems to me this should be a Package level setting.  If you know of a better or easier way to handle this problem, please share it in the comments below.

About these ads
Categories: Database, SQL Server, SSIS
  1. Peter
    October 14, 2010 at 2:33 pm | #1

    I’ve hit this before and it’s pretty annoying. Even worse because the time to validate a non-existing connection seems interminably long. It would be really nice to have an option to make that the default for all new objects in the package and then enable validation for those items you want it to use.

    I’m sure there’s a reason for this behavior, but it’s definitely been frustrating.

    On a related note, if you view the code, you can do a global replace to set it to True for everything. I’ve done that before and it’s helped when opening packages.

  2. October 14, 2010 at 2:51 pm | #2

    This is going to sound terribly ignorant, but I didn’t know I could view the code. I’ll have to look into that, thanks!

  3. October 15, 2010 at 3:06 am | #3

    Nice work.To improve visitors to your site,you can submit your links in http://codehunts.com

  4. October 16, 2010 at 9:00 am | #4

    Joel,

    You can use the tool SSIS-DTS Package Search verify that all the DelayValidation property is set to True in all packages. http://www.gerasus.com/

    Norman

  5. Keith Harris
    November 3, 2010 at 11:41 am | #5

    I use one package configuration file for each package. Here I set the DelayValidation value as well as other properties such as connection string values, it works very well.

    I have differently named config files for each environment and I use a batch file to copy the correct configuration to the package config file which all packages are using.

    i.e. If all packages use pkgconfig.dtsConfig, I have environment-specific config files named env1.dtsConfig & env2.dtsConfig; a batch file will copy the correct one to pkgconfig.dtsConfig (which all packages reference).

  6. Donald Neideffer
    June 29, 2011 at 3:31 pm | #6

    If you put data flow tasks in a sequence container, you can just set the DelayValidation property to True on the container and it will affect the contents. I can set DelayValidation equal to False during design time and quickly switch back to True when I am done.

Comments are closed.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: