blogs.conchango.com

Thanks for bringing this up again, Jamie. The fact that we can't "pass in parameters" to an SSIS package (such as by using /SET) and have those parameter values automatically cascade to all child packages (such as by using /SETCASCADE or /SET and /CASCADE, if they existed) is such a huge and obvious gap I can't believe that people aren't falling over backward trying to plug it.

Sorry - I've had a long week. ;-)

In any event, this is a problem that affects many SSIS users, but I do not believe that there is any one "correct" solution. Just 10,000 different workarounds...

PingBack from http://www.autocarsinsurance.net/ssis-parent-package-configurations-yay-or-nay.html

Our approach to configuring SSIS packages seems quite different to those mentioned above.

We use a combination of using an environment variable (STDCONN) and SQL configurations (SSIS_Configurations table).

The SQL config is being read by an OLEDB connection manager whose connection parameters are being set

by the configuration which is referenced by the environment variable.

So if you transport a package to another environment (e.g. from DEV to QA or PR) all your relevant configurations

reside in the SQL-Server which belongs to that environment.

To separate and distinguish different Package configurations, we use hierarchially named categories in the ConfigurationFilter attribute of

the SSIS_Configurations table. Hence we are able to use specific portions of the whole bunch of configs and can avoid unwanted warnings.

That means every package has at least two configurations:

* The config STDCONN for the location of the SSIS_Configurations table and

* One ore more further configurations from SSIS_Configurations (one config for each desired category)

Of course this method comes with some caveats. One of them is that you cannot use one SSIS-Environment for different

production scenarios (altough we do practice that in some cases) because the name of the environment variable is unique.

I consider this not to be a workaround but a working solution. Developers are even able to open packages from different

projects because of our naming conventions, so I think it's worth the effort to maintain the configuration table.

As it happens I work with Sam - and I've posted about parent package configurations versus direct configuration for child packages in some detail on my blog: http://phil-austin.blogspot.com/2008/08/child-vs-parent-package-configurations.html.

In terms of solutions, although I have yet to look at it in detail, a couple of ideas occur to me:

1. Use a child package template - for commonly used configuration you could hard-code values into the package variables.

2. Use different configurations for development-time and deployment-time. You could use direct configurations when developing packages and once frinished, switch to using parent configuration when finished. A bit dangerous this since you could simply forget to do it - although this should be spotted if you have a test environment.

Anyway as I say in my blog post this is something I'll return to. SSIS configuration is something I've spent a bit of time on over the last few weeks...

Another useful post, Jamie. Thanks to all for the insightful whitepapers...

PingBack from http://www.pythian.com/blogs/1212/log-buffer-113-a-carnival-of-the-vanities-for-dbas

We have taken an approach very much like the one Fridtjof outlined - I commend it, it works well, and makes deployment very straighforward and managable.

Use of SQL configurations and sensible naming conventions for the configurations filters allow configuation to be selectively applied as needed to your packages. For example we have a 'Common' filter which applies common environment-specific settings used by most packages, with other filters denoting more specific usage.

In my view this is much more manageable, less onerous and error prone than setting up potentially numerous Parent Package Configurations across all your child packages.

Also packages relying on Parent Package configurations cannot readily be run in isolation - for example for debugging during development.

We do use some Parent Package configurations - generally limited to information which cannot be predetermined in static configuration - for example is a package being invoked as child or not.

One difference in our set up compared to Fridtjof's overcomes the limitation of being unable to run separate environments on the same SSIS server due to the uniqueness of the environment variable. Less of consideration with the growth of virtualised servers I suppose, but one which we still need to put up with in our infrastructure.

Use an xml configuration file specific to each logical environment (Test,  Support,  UAT etc.) to hold the connection properties to the relevant SSIS SQL configuration database. Within the package, define  the xml configuration without any absolute path - just the name of the configuration file.

Have a specific single folder for each environment holding the relevant configuration file. The packages can be located wherever you like, and can be shared between environments if that is appropriate.

Now just ensure that when a package is invoked via DTExec, the working folder for DTExec is set to the folder holding the configuration file for the required environment. Everything then falls into place. The package picks up the correct configuration file and connects to the required database to pick up the rest of its configured settings. And it works fine for any child packages that are called in-process.

We have a formal mechanism to construct the required DTExec command line which amongst several other things passes the path to the package folder as a /SET argument, allowing simple run-time configuration of file connection managers to co-located child packages. Child packages located elsewhere can be defined in a SQL configuration

Packages can be invoked in this way by any suitable programmatic menas - we call packages from SQL procedures, SQL Agent jobs, Batch Files and Scheduled Tasks. With little effort such SQL procedures can and should be made location-independent too - easing the deployment task.

In Visual Studio the packages work in debug without any changes at all. The requirement here is that the solution, project, xml configuration, and packages are in the same folder.

Deployment involves setting up the SQL Configuration entries, then copy and paste the packages.

PingBack from http://www.mynetfaves.com/tags/logfile

I found that when debugging, I was passing a variable from one package to another.  The Package being called defined the Package Variable as well as a few others that weren't passed.

Seems that SSIS is polite enough to warn us that a defined Package variable didn't exist and wasn't set, however, it also didn't bother setting any more Package Variables after the warning.

Had to create variables in the calling package even though they weren't used to get around this.

Alex,

Your suggestion to "define  the xml configuration without any absolute path" worked like a charm. I just put all of our configuration in the XML config file. Doing so made this whole ugly configuration mess disapper for us: there is no need to use SQL Server configuration; no need to use the /CONFIG option or environment variable to pass the name of the config file; no need to pass the package folder as a /SET argument (it's in the XML config file); and there is now only one package configuration per package (vs requiring SQL Server configuration *and* XML configuration for SQL Server configuration connection string). Our configuration needs are perhaps more simple than yours, so we don't require anything fancier.

Too bad this feature doesn't get more explanation... in the Microsoft documentation or on the Net.

Thank you!

This was extremely helpful.  Thanks to all of you for you advice!!

I have several child packages which all have their own configuration files. They work fine as expected. They all point to a global configuration file with the "LoggingMode" option set to "0". When i run each child package on its own, it runs perfectly and does NOT log any events to the sql server table. This is expected and am happy with this result.

HOWEVER....when i use a parent package (driver package) to run all child packages in sequence, for some odd reason, the child package seem to start logging. This is against their configuration. I have even gone so far as to explicitly turn off logging in the parent package and still no success.

Is this a SSIS bug ?

How is it that once all child packages are exectuted via the "Execute Paclage Task" in the parent package, that they begin logging? yet when run individually, they dont log ?

Thanks for your reply.

I dont really need the parent package to pass in parameters to the child packages. All the parent package does is fire off each child package one after the other. So my question is....why is it that when the child packages are run individually, they are able to read their config (from config file), and do what theyre supposed to do (not log)? yet when they are executed from some parent package, they ignore their own individual configurations and just start logging ?

I did read Davide Maur's blog entry and it seems that when a child package is run from a parent package, it will begin logging before it reads in its own configuration ? - very odd.

The only difference i see, is that in one scenario I am clicking a button to execute the child package individually (which works), and in the other scenario, another package is taking care of that and automating that process for a series of child packages (which doesnt work as expected).

Also, the same sets of packages run about 4X faster in SSIS 2008 (they are SSIS 2005 packages) as opposed to running much slower in SSIS2005. And whats even more startling is that im using a foreach container. So its only iterating one item at a time, so how on earth can this be sped up without a newer more effective archirtecture i dont know. what ever it is, SSIS2008 is way faster.