Hi,
For some reason, I stay away from executing a package from a package. I guess that this is because I have experienced issues with this in the past and because I have read some articles in that forum that pin point specific issues when you do this.
So far I try very hard to either combine all I need in one package or I break it down in multiple packages and use SQL Agent to drive the package chaining.
Does anyone has a better experience with SP2 as far as running child package from parent package is concerned? Any bugs remaining out there?
Thanks,
Philippe
Hi philippe,
Have You given a thought on Thread Syncronzation Errors and Warnings?|||
Running SP2 here, no problems at all with parent-child package operation. The key factor is using a package configuration for shared info like connection strings. I found development and testing to be a lot easier to manage when a large package was split up.
|||I also have a lot of packages running as Parent/Child. Sometimes they fail, but this could be related to different causes.|||I'm not running SP2 yet, but I've seen quite a few people start to lean on SP2 as causing most of their recent issues. Please, if you have specifics, post them here as such so that we can diagnose them and get them documented. Many of us will be looking to move to SP2, but will rely in part on outside experiences.
As far as SP1, I run ALL of my packages from a parent. Always. Even if I have just one package that does something. This is part of my standards that I've put in place primarily for auditing purposes. Not to mention the master package is the only piece that I have to schedule. Regardless, I have no issues!|||
 Philippe wrote: |
|
| Hi, For some reason, I stay away from executing a package from a package. I guess that this is because I have experienced issues with this in the past and because I have read some articles in that forum that pin point specific issues when you do this. So far I try very hard to either combine all I need in one package or I break it down in multiple packages and use SQL Agent to drive the package chaining. Does anyone has a better experience with SP2 as far as running child package from parent package is concerned? Any bugs remaining out there? Thanks, Philippe |
I disagree. I don't know about your specific scenario but using parent package to call other packages have worked just find for me (and I am not running SP2 yet). Would you give specific problems that you have faced when try to use that approach?
BTW, personally I prefer to have a collection of simpler packages rather than a super-big one. Overtime I have found that this is a plus in multi-developers environments. Maintenance, troubleshooting and restorability are also easier that way .
|||
Hi,
I agree with the idea to avoid a super big and slow opening package while it depends if you pull everything at the same time or if you pull at different times different pieces.
For the package calling other packages, I was concerned about variables scope and configurations. in SQL2000 I had a super package running all others but it was uneasy. I felt more comfortable controlling the packages from jobs by either creating a job firing multiple packages or by creating multiple jobs.
Let say that one of the packages fail on the pre-validation step (SP1) then you had a much quicker view of which package failed with jobs that if it was a chain of packages. i pull from multiple Oracle boxes and sometimes one can be offline for a while.
The job let you easilly re-run only the failed package, it can also be set to retry, which is different than setting-up the package to resume at the point of failure, the job retry is a fresh start and can be scheduled to try n times every 15 minutes as an example.
Since users in this forum do not have problem with chain of packages, I may re-use this approach with much more confidence. now that the pre-validations error (SP2) gets logged, it should be much easier to trouble-shoot.
Regards,
Philippe
|||Sorry - but I can't seem to get the point of running a package from a package from a package.
For me it's same as Microsoft syntax - DELETE FROM table FROM table x ...
Variable scope & config:
I use a package template - which sets up most commonly used variables & connections.
Then I have 1 master config file which shared with most packages (db connection string, root path for directories etc.)
Errors & troubleshooting:
Event_Log table - populated by Exec SQL task on package success and failure
(also - logging is configured for additional package info - havent seen much use for it yet - as event_log usually suffices - perhaps useful for warning messages)
Jobs:
Packages are set up as job steps in 1 job --> downside to this is that MS SQL Server does not allow you to run just a single step - you can start at last step though to get around.
My moto is Keep it Simple - the more you do stuff like " package from a package from a package" - the more you set up yourself for headaches along the way|||
| TheViewMaster wrote: |
|
My moto is Keep it Simple - the more you do stuff like " package from a package from a package" - the more you set up yourself for headaches along the way
|
I personally keep it to two levels, the main parent package, and all of the children underneath that. I don't keep nesting packages. But, to each their own, and whatever works for you is just great!|||
Totally agree with Phil. I also use only 2 level of packages. At the end, there are many variables that need to be considered when choosing the right approach.
Just to get the discussion back on track; I don't know about any issue with parent packages that prevents you from using them.
|||
I've only been working with SQL Server 2005 for a few weeks (but SQL Server 2000 for a few years). I have a single SSIS package that invokes 6 SSIS packages, each of which invokes 3-5 SSIS packages, each of which invokes 3-8 stored procedures. The outer package takes approx. one hour to run.
My main task is using SQL to perform lots of computations that are well suited to a database (mostly simple SUM(x) and SUM(a*b) type expressions -- but with lots of data, and "business rules" that are coded somewhat via tables, and somewhat via SQL statements, and often in need of "tweaking" to accommodate special cases). I often look at this work as a "data factory" that receives huge "raw materials" data tables, and then performs much "value added" processing to arrive at the "product", which is essentially other data tables.
Just as an automobile has major components, so, too, do the output "product" tables. So, with the automobile analogy, I have "engine" output tables, and "transmission" output tables, and "body" output tables. But in the "engine" output tables, I have sub-components, such as "fuel systems", "electrical systems", etc. And I have similar "sub-components" for the "transmission" and "body" output table sets.
So I have an overall "automobile" SSIS package that calls sequentially the major component SSIS packages ("engine", "transmission", "body", etc.), which each call internally their respective sub-component SSIS packages. These sub-component SSIS packages call stored procedures. Sequential processing is necessary because some procedures rely on the computations from earlier procedures. (In some cases I even use the same "assembly line", and just change small portions of the "input data stream" to produce a different "data product" -- because of the similarity of business rules for the two output data sets; this is similar to how the same cheese processing equipment can be used to make cheddar or Monterey Jack; but other assembly lines are making vodka, for example, with much different business rules.)
So at the moment I have a 3-level package arrangement. When I run the highest-level package I get answers back in approx. 1 hour on a quad-processor, high speed server. (Not a supercomputer by any means. And while it runs it means I am free to write more code, perform data QA/QC studies, etc.)
My greatest speed improvement, aside from making suitable indexes on tables, came from a better choice of "fill factor" on the indexes of some of the intermediate process tables: all the INSERT statements at different processing stages were initially forcing too much activity in maintaining the indexes. (It was a strange problem to track down, since if I re-ran the procedure that failed due to a time-out, it would always run very quickly the second time. That's because the INDEX had expanded itself while the procedure was running out of time, and remained expanded when I performed a "DELETE ... WHERE ..." from the table as one of the initial steps in the stored procedure. So when it ran the second time there was plenty of room to make the INSERT entries into the INDEX tables/pages.)
I can easily imagine situations wherein 4 or more levels of packages would constitute a "natural" design.
Dan
malhar wrote: