Run 100+ SSIS packages in parallel from a parent package

Question

I have 100+ child packages and I need to run them in parallel from a parent package. For this I will have to create 100+ Execute Package tasks and then 100+ File Connections. This doesn't look appealing to me and it is repetative and error prone. Is there any other way to do this. Keep two things in mind.

1. Child package Execution should be in parallel (so no For loop and stuffs)

2. I am using CheckPoint based restart-ability and hence need control flow items at compile time (no script component based solutions too)

Answer 1

This is an interesting question, but a concern of mine would be how many packages you can possible run concurrently on the server you’re using.

If the number of threads exceeds the number of available processors you have, you might end up hurting throughput due to an excessive amount of context switches.

So before we even fathom an answer, do you have the hardware to do this? Is it even possbile to run that many in parellel?

Answer 2

Does it have to be file connections? Have you looked at the options of having the packages stored in the SSIS package store and referencing it from there.

You would still have your 100+ components, but not your 100+ file connections.

Answer 3

I give up. There is no way AFAIK. I decided to create 100+ jobs, one job per package and using the same schedule. Creating jobs was easier using Dynamic SQL.

Answer 4

There probably are ways but you would have to be either very rich or very dumb to do it. What kind of machine do you have with 50 processors or whatever???