Hi I have a question about best practice with package grouping and control that I was hoping to get some opinions on :) We have around 200 simple text file extracts each with its own package. These can be grouped into about 20 meaningful jobs. Currently I'm considering the options available to group these up. I'm aware of full fledged frameworks where information is stored in a database, and the packages executed in a for each loop. However I want to stay away from that approach or fully fledged ETL frameworks that support re-running etc. It is just too much for our needs, as mentioned these are very simple text extracts, although there is a lot of them. So far I've investigated the old master package idea which executes each package as a child but I am put off by the limited support for setting variables in the child without modifying it to use parent package variables. Have also thought about 20 Agent jobs with x steps in each.This seems the simplest and most basic option and has the bonus of being able to set variable values from in SQL Agent, as well as allowing us to change package configuration and logging providers from within SQL Agent. Can anyone comment on similar experiences or ideas for grouping/execution control of large numbers of simple text extracts? Is there anything wrong with simply having a large number of agent jobs with several execute package steps - ie. what distinct advantages would a parent-child framework give in this situation? Thanks in advance Josh

Think: FAILURE What happens if Step #42 of 200 fails in SQL Agent? There is no elegant way to figure out exactly where to re-start. With SSIS and its functionality of Check-Point files, you can group similar blocks of logic into one container and set the appropriate properties of the containers so that if you fail in the middle, a re-start will run JUST the missing tasks. My other thought is this: Keep it all in one 'system', either SSIS or SQL Agent. Why group 20 packages into a sub-master if you are going to have 10 sub-masters? Why not just go for ONE master that contains all the Flow-Control logic? Hope this helps. And what's your issue with Parent Variable configuration? Why do you not like them?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks Todd that does help.Your point with failures and checkpoints is a really helpful one. My issue with configuring from a parent is that the package is then dependent on the parent. It would be nice to be able to set variables in the execute package task as well as configurations - this is being added to Denali?I saw a connect article about it where MS were saying they were adding that functionality. Anyway I think the checkpoint functionality you've mentioned has sort of swayed the whole thing in favor of control packages, even with the limitations of the execute package task. Thanks very much for your help! (If you have any further suggestions they would be much appreciated!)