THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SQL Server Configuration timeouts - and a workaround [SSIS]

Ever since I started writing SSIS packages back in 2004 I have opted to store configurations in .dtsConfig (.i.e. XML) files rather than in a SQL Server table (aka SQL Server Configurations) however recently I inherited some packages that used SQL Server Configurations and thus had to immerse myself in their murky little world. To all the people that have ever gone onto the SSIS forum and asked questions about ambiguous behaviour of SQL Server Configurations I now say this... I feel your pain!

The biggest problem I have had was in dealing with the change to the order in which configurations get applied that came about in SSIS 2008. Those changes are detailed on MSDN at SSIS Package Configurations however the pertinent bits are:

As the utility loads and runs the package, events occur in the following order:

  1. The dtexec utility loads the package.
  2. The utility applies the configurations that were specified in the package at design time and in the order that is specified in the package. (The one exception to this is the Parent Package Variables configurations. The utility applies these configurations only once and later in the process.)
  3. The utility then applies any options that you specified on the command line.
  4. The utility then reloads the configurations that were specified in the package at design time and in the order specified in the package. (Again, the exception to this rule is the Parent Package Variables configurations). The utility uses any command-line options that were specified to reload the configurations. Therefore, different values might be reloaded from a different location.
  5. The utility applies the Parent Package Variable configurations.
  6. The utility runs the package.
To understand how these steps differ from SSIS 2005 I recommend reading Doug Laudenschlager’s blog post Understand how SSIS package configurations are applied.

The very nature of SQL Server Configurations means that the Connection String for the database holding the configuration values needs to be supplied from the command-line. Typically then the call to execute your package resembles this:

dtexec /FILE Package.dtsx /SET "\Package.Connections[SSISConfigurations].Properties[ConnectionString]";"\"Data Source=SomeServer;Initial Catalog=SomeDB;Integrated Security=SSPI;\"",

The problem then is that, as per the steps above, the package will (1) attempt to apply all configurations using the Connection String stored in the package for the "SSISConfigurations" Connection Manager before then (2) applying the Connection String from the command-line and then (3) apply the same configurations all over again. In the packages that I inherited that first attempt to apply the configurations would timeout (not unexpected); I had 8 SQL Server Configurations in the package and thus the package was waiting for 2 minutes until all the Configurations timed out (i.e. 15seconds per Configuration) - in a package that only executes for ~8seconds when it gets to do its actual work a delay of 2minutes was simply unacceptable.

We had three options in how to deal with this:

  1. Get rid of the use of SQL Server configurations and use .dtsConfig files instead
  2. Edit the packages when they get deployed
  3. Change the timeout on the "SSISConfigurations" Connection Manager

#1 was my preferred choice but, for reasons I explain below*, wasn't an option in this particular instance. #2 was discounted out of hand because it negates the point of using Configurations in the first place. This left us with #3 - change the timeout on the Connection Manager. This is done by going into the properties of the Connection Manager, opening the "All" tab and changing the Connect Timeout property to some suitable value (in the screenshot below I chose 2 seconds).

connman

This change meant that the attempts to apply the SQL Server configurations timed out in 16 seconds rather than two minutes; clearly this isn't an optimum solution but its certainly better than it was.

So there you have it - if you are having problems with SQL Server configuration timeouts within SSIS try changing the timeout of the Connection Manager. Better still - don't bother using SQL Server Configuration in the first place. Even better - install RC0 of SQL Server 2012 to start leveraging SSIS parameters and leave the nasty old world of configurations behind you.

@Jamiet


* Basically, we are leveraging a SSIS execution/logging framework in which the client had invested a lot of resources and SQL Server Configurations are an integral part of that.

Published Wednesday, November 30, 2011 10:43 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

swoopske said:

I recently started using a solution that is kind of a hybrid. There is a .dtsconfig file which stores connection data for the sql configuration only - it is set to be the first configuration in the package and it's path is stored in environment variable. The rest of configuration parameters are stored in sql. It works quite nice.

December 1, 2011 3:57 AM
 

gunther said:

I was going to leave a comment just like swoopske's, we're doing the exact same thing where I'm at (and also leveraging a SSIS execution/logging framework). One of the things I like about it is that the name of the environment variable is the same on every server, but on the dev servers the .dtsconfig file has a connection string pointing to a dev database where I can keep dev-only SQL Server configurations.

December 1, 2011 9:21 AM
 

D. Pendleton said:

Agreed, store the connection string in a .dtsConfig file and access it via environment variable (indirect configuration). I use this approach for my configuration, metrics and logging connections.

In five years of writing SSIS packages, I've never passed a variable value on the command line.

December 1, 2011 3:15 PM
 

Brett Gerhardi said:

Agreed with other comments, the path of least resistance in my experience (and to remove any file maintainance/deploys which I dislike) is to use a hardcoded environment variable for the Sql Configuration.

I'm a big fan of Sql Config I find as your config is usually tied to a particular DB instance (dev, uat, live etc) and this way you get all config changes with a single change (in this case Env Var) - it also allows you to create static data scripts in VS 2010 database projects to manage the config according to the staging platform you are deploying to. Often between configs there are some configs that don't change between stages and some that do - this can be elegantly managed in a single script and the intention and changes clear to developers to help them understand the system.

Note that also it should be possible to apply the environment variable as part of a batch file that calls DtsExec so it is effectively the same in implementation as a Ssis setvar without all the fiddling around that Jamie indicates to work around using them in the Ssis configuration mechanisms. You don't need to set the environment variable at the system level (which often can be a problem for production systems).

December 13, 2011 5:31 AM
 

jamiet said:

Hi Guys,

Yes, Environment Variable is indeed a solution here and with hindsight perhaps I should have emphasized the problem-and-workaround here rather than being disparaging about SQL Server configs. Still, Env Vars bring their own problems too and I'm no great fan of those either. My opinion is that there is no "good" way of doing this in SSIS2008 so I can't wait until I'm developing on SSIS2012.

regards

JT

December 13, 2011 5:59 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement