Configurations

Hello, I had a question I thought someone might be able to answer before I test my theory.

If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.

If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?

Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?

Any thoughts?

Okay, I think I'm answering my own question, but perhaps others can benefit.

I think it is best to configure configurations in SSIS indirectly through an environment variable and then set the environment variable through sql server agent - command file tab, which will use the SetX command to modify the environment variable. This way all packages outer and inner can use the same dynamically configured configuration file! Although I supposse if multiple jobs tried to configure the same environment variable there could be issues, you'd have name your environment variables appropriately.

Thoughts?

|||

Configuration are not inherited between packages as you suspected.

I think you can say using indirect config files is good practice, and making you devs have the same env variable is the way to go. It actually works very well I find. I like the fact that I use the same mechanism to control connections and such like all the way from local development through to production. Configurations just work so well like that.

The Set tab in SQL Agent is not for environment variables, Set in that case is the same type of set that you do in the config file, xpath style property set, but available on teh execution command rather than wrapped in a file.

BTW, use CmdExec steps and DTEXEC, much better as you can use the step log.

|||Thanks for the response. Could you please clarify what you mean by your last statement?|||

The SSIS job step type is not very good. The CmdExec / Operating System step type supports logging of all output of the step. If you use DTEXEC and turn on step logging you get all the console output. Why is this good? Well put simply the SSIS step log capture is pants. Some errors are impossible to diagnose with it. A quick examples are file permissions, or other errors that happen before a package starts executing, see the top bit of this page -http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||Okay, I understand what your saying and completely agree. This is how things were done at a prior company, but here they prefer using ssis job steps for the ease of seeing what package is running. That is why I wanted to work around configuring environment variables through SQL Server Agent istead of within a batch file. Still, logging may be insufficient as you pointed out. Thanks.sqlsql