Execute Child packages parallel in loop

Question

Execute Child packages parallel in loop

jjr.123

Old Hand

Points: 308
More actions
July 11, 2013 at 10:35 pm

#279897

hi ,
I have a master package which calls the child packages from a folder dynamically, when i tried this with execute package task inside a for each loop container , it is only looping the child package which is connected in the execute package task connection.
Could any please suggest me a way to achieve this task. I want the parent package to read the 1st child package , start and read next package, start and ....read last package, start.
Thanks in advance

Viewing 11 posts - 1 through 10 (of 10 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Did you put an expression on the Execute Package Task?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sneumersky SSCertifiable Points: 7667 More actions · Answer 2

If I am not mistaken, using one execute package task (EPT) within a loop would execute the packages serially, right? Parallel EPT execution would require multiple EPTs that were not connected to each other.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

jjr.123 (7/11/2013)
hi ,
I have a master package which calls the child packages from a folder dynamically, when i tried this with execute package task inside a for each loop container , it is only looping the child package which is connected in the execute package task connection.
Could any please suggest me a way to achieve this task. I want the parent package to read the 1st child package , start and read next package, start and ....read last package, start.
Thanks in advance

Kind of a kludge, but can be done by:

1. Have Int32 var increment by 1 each time through FeLC.

2. Have extra String var to store "last pkg name" so you have access to last pkg and current pkg

3. Have dummy Script Task in place to allow for Precedence Constraint, with two connections to Execute Package Tasks and a constraint that only proceeds when the Int32 variable is divisible by 2.

One of the Execute Package Tasks will run the var "last pkg name" and the other will run the loop's current pkg.

You'll need some logic to process the last pkg in case there were an odd number picked up by the loop, but that's the gist of it. Also know that both pkgs need to complete before the loop continues. Using two loops aide by side that iterate over the same folder and process a mutually exclusive set of pkgs is also an approach that could work.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

jjr.123 Old Hand Points: 308 More actions · Answer 4

Hi Can you please share me a sample package that shows how to configure int32 variable to increment by 1 ,

other string variable to hold the packageName , and loop through packages in folder as u told above.

Thanks in advance

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

sneumersky (7/12/2013)
If I am not mistaken, using one execute package task (EPT) within a loop would execute the packages serially, right? Parallel EPT execution would require multiple EPTs that were not connected to each other.

You're right, that would be needed. 4 EPT tasks would be a good start I think.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

jjr.123 (7/14/2013)
Hi Can you please share me a sample package that shows how to configure int32 variable to increment by 1 ,
other string variable to hold the packageName , and loop through packages in folder as u told above.
Thanks in advance

How about you start designing the package and ask a question when you run into problems, instead of relying on other people to do your job?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

jjr.123 (7/14/2013)
Hi Can you please share me a sample package that shows how to configure int32 variable to increment by 1 ,
other string variable to hold the packageName , and loop through packages in folder as u told above.
Thanks in advance

You've been given several viable options in the form of free guidance on a public forum from volunteers who also happen to do this for a living. If you would like help producing the actual deliverable send me a Private Message or email and I am sure we can work out a fair hourly rate. If you get more into producing a solution on your own and have questions, feel free to post back to this thread or start a new one.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

Visit Books Online and go down the SSIS tree. There are descriptions for every control flow item in there. Once you have a feel for that item start tinkering with it. This will provide you a better learning experience that quick answers answers that you will later forget, because you don't have the knowledge foundation to integrate the answers into what you already know.

----------------------------------------------------

lijnenjoost Right there with Babe Points: 767 More actions · Answer 9

jjr.123 (7/11/2013)
hi ,
I have a master package which calls the child packages from a folder dynamically, when i tried this with execute package task inside a for each loop container , it is only looping the child package which is connected in the execute package task connection.
Could any please suggest me a way to achieve this task. I want the parent package to read the 1st child package , start and read next package, start and ....read last package, start.
Thanks in advance

Here is a parallel loop example with a queue:

http://microsoft-ssis.blogspot.com/2013/11/execute-mutliple-child-packages-in.html

PB_BI SSCoach Points: 17463 More actions · Answer 10

Koen Verbeeck (7/15/2013)
How about you start designing the package and ask a question when you run into problems, instead of relying on other people to do your job?

Haha brilliant! 😀

To the OP - you will learn more if you actually read up on these things yourself rather than being spoon fed.

I'm on LinkedIn