[SSIS] Parallel execution

Question

[SSIS] Parallel execution

SQL.AP

Hall of Fame

Points: 3244
More actions
November 9, 2015 at 10:25 pm

#312994

I am planning to re-design one of my SSIS processes for performance upgrade.
One package is calling various child packages. I want to remove any physical dependencies and execute all packages in parallel. (There are around 50 packages)
I will store all dependencies in SQL and add a dependency check inside all the packages.
Please suggest, what would be the best design to execute these packages, execute all in parallel (I know it depends on the number of cores, so only that number of packages will execute in parallel)
Or shall I add physical dependencies again?
Or please suggest what could be the best option.
ta
____________________________________________________________
AP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

SQL.AP (11/9/2015)
I am planning to re-design one of my SSIS processes for performance upgrade.
One package is calling various child packages. I want to remove any physical dependencies and execute all packages in parallel. (There are around 50 packages)
I will store all dependencies in SQL and add a dependency check inside all the packages.
Please suggest, what would be the best design to execute these packages, execute all in parallel (I know it depends on the number of cores, so only that number of packages will execute in parallel)
Or shall I add physical dependencies again?
Or please suggest what could be the best option.
ta

May I ask why you removed the dependencies initially? SSIS is pretty good for that.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL.AP Hall of Fame Points: 3244 More actions · Answer 2

There are various interfaces which are independent, so they would be executed in parallel. However, we've a changing req. and in future there would be more interfaces coming and we can directly put an entry in the dependency table in SQL and execute that package directly.

____________________________________________________________

AP

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

SQL.AP (11/10/2015)
There are various interfaces which are independent, so they would be executed in parallel. However, we've a changing req. and in future there would be more interfaces coming and we can directly put an entry in the dependency table in SQL and execute that package directly.

As you want to control your dependencies via meta data rather than having to make SSIS design changes, I would suggest that you need some sort of SSIS ETL Framework which can operate from such meta data.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL.AP Hall of Fame Points: 3244 More actions · Answer 4

Yep, that's already in place.

The only thing which I am concerned about is running multiple packages in parallel. I have 16 processors running on my server and I want to execute around 50-60 packages in parallel.

I can update the max concurrent property but I have other processes as well running on my server simultaneously, would you suggest executing all in parallel? or in sets?

ta

____________________________________________________________

AP

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

Hopefully your framework allows you to set the max concurrent number of executing packages per job.

I'd suggest playing with that number a bit. Start on the low side and gradually increase it, while monitoring the effects.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SQL.AP Hall of Fame Points: 3244 More actions · Answer 6

Ok, I will work on it.

cheers.

____________________________________________________________

AP