Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.