Developing For .NET

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.