I'm currently looking at refactoring an existing, large SSIS 2012 implementation that consists of about 55 projects and 250+ packages. The ETL framework that is in use has a "main" control package that reads from a database table and determines which packages are ready to execute (based on some dependency logic) and then uses an Execute Process task within a loop that calls dtexec with the arguments: 

/C start Dtexec /SQL "SomePackagePath" /SERVER "someserver" 

This design allows the loop to execute a package and then immediately iterate because it doesn't wait for the package to respond (aka complete with a failure or success) so it can quickly kick off as many packages are ready to execute. A SQL Agent job is used to call this package every few minutes so that it can pick up any packages that have had their dependencies satisfied since the last execution and kick those off.

It's a a clever design but has some problems such as decentralized exception handling (since the parent package is unaware of what is happening in the "asynchronous" dtexec calls.

My biggest concern is that by executing packages not with the Execute Package Task but with the Execute Process Task and spinning up many dtexecs, the framework is not leveraging SSIS's ability to handle threading, memory consumption, etc. across all running packages and executables because it is simply unaware of them. It's essentially like using an Execute Package Task with the ExecuteOutOfProcess property set to true.

I'm wondering how much risk is introduced by this design and if there are any approaches I can take to minimize the risk of excess resource consumption. I apologize for not having an exact question here but would really appreciate any guidance anyone might have.

• Edited by David Elish (infoco) 4 hours 13 minutes ago