June 21, 2012 at 7:41 am
We have a rather extensive process which is comprised of many simple SSIS packages. Ultimately, the process will be launched by a SQL Agent job on a schedule. What I am debating is whether I should set up the SQL Agent job to call a single parent package which calls each of the individual packages or whether I should set up the SQL Agent with multiple steps with each step call one of the individual packages.
Additional info. Of all of the simple packages, there is a small subset which must run first, then, once those are completed, any of the remaining packages can execute in any order or all at once. I want to insure that I am maximizing the potential parallelism in the execution process of these packages.
Thanks in advance,
Scott B Dragoo
Database Administrator
Yield Technologies
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
June 21, 2012 at 10:52 am
Most of mine have a parent package that will fail if any children fail. Presumably you could do the same thing by making each child a package step and setting whether entire job fails on a child failure or goes to next step.
We make use of auditing so the parent package key is of interest to us and every child uses it. I'm not sure you could accomplish the same with disparate packages but I hope to learn something in this thread.
We also only have to set the config variables once for the parent package, but if you use a different config method, I'm not sure what the differences are in parent pkg vs mult steps.
June 25, 2012 at 8:49 am
It will be interesting to note the thoughts of the community on this.
The architecture I have designed and deployed goes as follows
1. An sql agent jobs that runs in 3 steps. Each step contains a master package with various child packages.
2. I've broken the 3 master packages as "logical" steps in the data transistion. Master I contains the staging/load child packages. Master II contains the transformation/cleansing/massaging element of the data. Master III contains the dimesion/fact tables loading plus a success constraint to process the cube elements in a sequence container.
3. So i've structured the solution really in an E-T-L stage flow. Each of the stage involves parallel packages execution.
This solution has worked well for me over the past couple of years for day in day out processing. Overalll, i believe having a master package with as many child packages that do not have any INTERDEPENDENCIES is a good idea. SSIS and SQL will always optimize the package execution and manage resources effectively.
There will always be ways to improve the architecture but i'm not sure there can be a one size fits all approach to this. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply