November 9, 2015 at 10:25 pm
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
____________________________________________________________
APNovember 10, 2015 at 12:47 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 10, 2015 at 3:22 am
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.
____________________________________________________________
APNovember 10, 2015 at 4:12 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 12, 2015 at 8:00 pm
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
____________________________________________________________
APNovember 13, 2015 at 12:36 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 13, 2015 at 4:08 am
Ok, I will work on it.
cheers.
____________________________________________________________
APViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply