A few of my fellow SSIS MVPs and I were asked if we wanted to submit a whitepaper to coincide with the new release of SQL Server; today those papers were (bar one) released:
Quite a common theme there, I guess Matthew, John, Rafael and I should have conferred before we started writing :)
My paper discusses a technique that could be used to eliminate the use of external configurations by instead using the /SET and /CONFIGFILE options of dtexec.exe in conjunction with Parent Package configurations. I have implemented this approach on a recent project and hence have come to experience one of its limitations that I alluded to in the paper:
"There are also disadvantages to using the /SET and /CONFIGFILE options. For example, the Execute Package Task does not have a similar feature that reproduces their behavior. Therefore, if you have a solution that uses parent and child packages, you will probably find that you need to make use of parent package configurations."
In said project I wanted to use one log file per package and pass the folder location of those log files from the master package to child packages using a parent package configuration. When I tried this I was quickly reminded of Davide Mauri's blog entry Beware of Parent Package Variable configuration! where he explains the order in which certain events occur at the beginning of a package's execution:
- All configurations other than parent package configurations are applied to the package
- Logging starts
- Package is validated
- Parent Package Variable Configurations are loaded
- Expression are applied
- Package is run
As Davide says:
"if you need to make the logging connection manager configurable at run time you MUST avoid using Parent Package Variables"
At best the implication of not doing this will be that your package logs to the wrong file. At worst it will attempt to log to a non-existent location and the package will fail before it has even started. I chose not to use one log file per package and instead have a single log file in the master package.
This limitation would not be a problem if the Execute Package Task supported the same options as dtexec.exe so I urge you to vote for the same at Execute Package Task should support parameters. The afore-mentioned Matthew Roche states at that link
"It would be difficult for me to agree any more with this suggestion. The lack of reuse in SSIS is a major barrier to productivity and usability, and is likely a major barrier to product adoption as well."
Phew. Don't mince your words Matthew!!!
On a very similar vein I was recently emailed by Sam Loud (Sam is one of this blog's perennial commenters for which I am very grateful) and he asked me to share the following:
When using child packages, is it better to use Parent Package configurations, or go straight to the configuration itself?
I'm using a classic Parent / Child package solution. The parent package is configured from SQL Server, via indirect configurations using environment variables. This all works just fine (although I hate the Windows environment variable editor).
I'm using the configuration to populate about 15 variables in the parent package - exactly what you'd expect; variables for connection strings, log files, SSIS_Root, all that sort of thing. Obviously, I need the child packages to inherit some of these configured items, particularly connection strings.
Currently I'm setting up parent package variables on the child packages, and pulling the variables I need down from the parent. This works just fine. However, when I want to debug a child package, it can't inherit the configured items from its parent, because it hasn't been called from a parent. This means blank variables, which means blank connection strings, which means it can't validate.
To get around this, I hardcode defaults into the variables in the child package. This is an awful workaround though, because I'm totally bypassing the configuration, and there may be many child packages that need changing. It also causes problems when moving packages, and violates all of the 'keep packages in the dark' best practices.
So, should I go straight to the configuration to populate the variables? This has the advantage that I can debug the child package and it will configure according to the configuration, rather than the hardcoded variable defaults. One problem here is that the SQL Configuration has 15 configuration items, but the child package only requires say..2 variables to be configured. This means 13 warnings will be thrown, because 13 of the configured items will fail to find their destination variables in the child package. I don't want my packages to be throwing warnings.
Surely some of you must have come to an optimum solution here. Please share. I'd be interested to hear your approaches and rationale. Hopefully this will lead to a new best practice; one that has been validated by the community.
One workaround to Sam's problem is to set the package property SuppressConfigurationWarnings=TRUE (its set to FALSE by default) but to be honest that's just a problem avoidance, not a problem solution. My personal opinion is that there is no optimum solution because there are so many variable scenarios - not many will have 15 configurations like Sam. What say you?