Apr
7
2009

Understand how SSIS package configurations are applied

This week I'm cleaning up our BOL documentation about how SSIS package configurations are applied, and how the behavior has changed between SQL Server 2005 and SQL Server 2008 Integration Services. These changes affect what you can and can't do to change your design-time settings at run time. My cleanup was prompted, as is often the case, by a kick in the butt from Microsoft Support and its customers. I admit that it has taken me a while to figure out all the ifs, ands and buts. SQL Server 2008 Books Online will have clearer explanations after the next release, around the beginning of May 2009.

Throughout this topic, I'm talking about running deployed packages by using the DTEXEC.EXE command prompt utility. For information about DTEXEC and the options that you can specify on the command line when you run a package, see dtexec Utility.

The big difference is...

In SQL Server 2005 Integration Services, things happen in this order:

  1. We apply design-time configurations; that is, the configurations that you created and associated with the package when you designed it in BIDS.
  2. We apply the run-time options that you specified on the DTEXEC command line.

In SQL Server 2008 Integration Services, things happen in this order:

  1. We apply design-time configurations.
  2. We apply the run-time options that you specified on the DTEXEC command line.
  3. We reload and reapply design-time configurations.

So what?

Here's where it gets interesting. Let's look at each of the 3 DTEXEC command line options that can be used to affect configurations, and compare the behavior between 2005 and 2008. These 3 DTEXEC command line options are:

  • /Conf[igFile]. This option does NOT change the location from which design-time configurations are loaded. Instead, it loads what we're calling run-time configurations separately. It's important to keep this distinction in mind, or the behavior of /CONF doesn't make sense.
  • /Conn[ection]
  • /Set

DTEXEC option

2005

2008

/CONN to change the connection string used to load design-time configurations

HAS NO EFFECT.

After DTEXEC options are applied, design-time configurations are NOT reloaded.

SUCCEEDS.

After DTEXEC options are applied, design-time configurations are reloaded from the new location.

/SET to change the location from which design-time configurations are loaded

HAS NO EFFECT.

After DTEXEC options are applied, design-time configurations are NOT reloaded.

SUCCEEDS.

After DTEXEC options are applied, design-time configurations are reloaded from the new location.

/SET to change a property value that is also set by a design-time configuration

SUCCEEDS.

The new value is NOT overwritten, since design-time configurations are NOT reloaded after DTEXEC options are applied.

HAS NO EFFECT.

The new value IS overwritten when design-time configurations are reloaded after DTEXEC options are applied.

/CONF to load run-time configurations for the SAME property values that are also set by design-time configurations

SUCCEEDS.

The new values are NOT overwritten, since design-time configurations are NOT reloaded after DTEXEC options are applied.

HAS NO EFFECT.

The new values ARE overwritten when design-time configurations are reloaded after DTEXEC options are applied.

/CONF to load run-time configurations for DIFFERENT property values than those set by design-time configurations

SUCCEEDS.

SUCCEEDS.

Parent Package Variables get special treatment. For more information, see John Welch's article listed below.

For more information...

Consider reading these articles by 3 of our SQL Server MVPs:


I hope that this information helps you to make sense of how SSIS package configurations are applied, and how you can tweak them at run time!

Please comment on my blog!

You don't have to sign up or sign in - anonymous comments are enabled. I "moderate" the comments to delete spam before you see it, but otherwise, I publish your comments immediately. You can also email me at dougbert@dougbert.com. Thank you!

-Doug

Comments (4) -

Rajesh

I am using the Configuration table in the SQLSERVER 2005 database to set the connection values in my package.


Also, I am using envinoment variable to load the connectionstring for the configuration table database. I have set up everything as DEV but SSIS is loading data in the QA environment. Is there a cache to clear. I understand the SSIS package shoud pick up the configuration from the package configuration if specified, but it is not doing so as I have removed the configuration table from the existing db and tried to run the package but it succeeded. Any help appreciated. Let me know how this config thing works in the design?


dougbert

Please ask technical support questions in the SSIS forum at social.msdn.microsoft.com/.../">social.msdn.microsoft.com/.../threads.


Thank you,


-Doug


rahul

What do you suggest the possible solution for below error.

Executing the query "isp*********" failed with the following error: "Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am using ADO connection destination.

dougbert

This is an ADO.NET connection pooling error and not an SSIS-specific issue. It's possible that you are opening and closing too many connections too quickly. You can find a lot of suggestions by searching the web for the error message, "The timeout period elapsed prior to obtaining a connection from the pool." Regards,   -Doug

Pingbacks and trackbacks (4)+