Event sequence of config options in SSIS runtime environment

[dwaine]

Sorry about that, this got a subject that was mixed with another post. The
correct subject appears here.

[Charles Wang [MSFT]]

Hi Dwaine,
Welcome to Microsoft MSDN Managed Newsgroup.

Regarding your questions, SSIS 2005 and 2008 have some different behaviors
on processing the package configurations. However I think that you should
be clear of the answers after you read the following two articles to get
more information:
Package Configurations
http://msdn.microsoft.com/en-us/library/ms141682.aspx
Behavior Changes to Integration Services Features in SQL Server 2008
http://msdn.microsoft.com/en-us/library/bb500430.aspx

Here is the extraction for your reading convenience:
==============================================
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.


The way in which the dtexec utility applies configurations affects the
following command-line options:

+ You can use the /Connection or /Set option at run time to load package
configurations from a location other than the location that you specified
at design time.

+ You can use the /ConfigFile option to load additional configurations that
you did not specify at design time.


However, these command-line options do have some restrictions:

+ You cannot use the /Set option to override single values that are also
set by a configuration.

+ You cannot use the /ConfigFile option to load configurations that replace
the configurations that you specified at design time
============================================

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Todd C]

This might help:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

(Yes, I know, shameless self-promotion, but it works for me and others have
found it useful.)

=====
Todd C

I'm really confused about the functionality of SQL (and files) configurations
in SSIS packages. Unless everything is configured exactly perfectly, SSIS
seems to just progressively try other options without any indication.
try to use a command line configured config file, if it doesn't exist (or
it's an UNC path), silently ignore, without even indicating it was
attempted...
try to use any design time configuration (files, SQL, etc), and if they
don't exist, silently ignore without even indicating they were attempted...
just use values defined in the package at design time.
For SQL config, I would expect to be able to create a SQL config group of
settings, copy that config table to different environments, changing values
where appropriate, and then create a SQL Agent jobs to run the package. In
that job, I would want to change the config table connection
(server/database) with "set variables" to change runtime behaviour and pull
data from the appropriate environment.
However, what seems to be happening is that the current configuration
settings in the package are used get the SQL config, and THEN the command
line set commands are being applied, so any attempted changes to the config
data source are useless.
could somone point me to a good reference that CLEARLY describes what
sequence of events happens during a DTExec run of an SQL package?
what rules are used when looking for and applying configs applied from
command line or design time.
when are set variables applied
when are configs applied
explain why do configurations on the command line seem to be ignored if a
config is already defined in the package at design time.
explain why are defined configuration files that don't exist simply go out
in a whimper, with no indication that they were or were not used.

[Todd C]

This might help:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

(Yes, I know, shameless self-promotion, but it works for me and others have
found it useful.)

=====
Todd C

I'm really confused about the functionality of SQL (and files) configurations
in SSIS packages. Unless everything is configured exactly perfectly, SSIS
seems to just progressively try other options without any indication.
try to use a command line configured config file, if it doesn't exist (or
it's an UNC path), silently ignore, without even indicating it was
attempted...
try to use any design time configuration (files, SQL, etc), and if they
don't exist, silently ignore without even indicating they were attempted...
just use values defined in the package at design time.
For SQL config, I would expect to be able to create a SQL config group of
settings, copy that config table to different environments, changing values
where appropriate, and then create a SQL Agent jobs to run the package. In
that job, I would want to change the config table connection
(server/database) with "set variables" to change runtime behaviour and pull
data from the appropriate environment.
However, what seems to be happening is that the current configuration
settings in the package are used get the SQL config, and THEN the command
line set commands are being applied, so any attempted changes to the config
data source are useless.
could somone point me to a good reference that CLEARLY describes what
sequence of events happens during a DTExec run of an SQL package?
what rules are used when looking for and applying configs applied from
command line or design time.
when are set variables applied
when are configs applied
explain why do configurations on the command line seem to be ignored if a
config is already defined in the package at design time.
explain why are defined configuration files that don't exist simply go out
in a whimper, with no indication that they were or were not used.

[dwaine]

Sorry about that, this got a subject that was mixed with another post. The
correct subject appears here.