Foreach loop with parallel execution

Foreach loop with parallel execution

• MLR

  Hi Michael,

  I am trying to do the same thing - execute several instances of a single child package parallelly.

  What I have done so far -

  1) I have the child package ready with all the configurations.

  2) The parent package has a For Each Loop Container with an Execute Process Task.

  The parent package is browsing through a list of files available in a single folder and passing the URL of the file as parameter to the child package instance. For each file it should call the same child package and the files should be processed in parallel. (Am I asking for too much )

  The Execute Process Task is using "dtexec" to execute the package.

  However, the child package is still executing sequentially.

  Ideally, if there are 4 files in the folder, they should be processed by 4 different instances of the same child package in parallel.

  Any thoughts on this

  Thanks in advance.

  Regards,

  B@ns

  
  

• NikiR

  Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true
  
  Regards,
  Lars
  
  

• ljlevend

  lasa wrote:

  I ended up using the System.Diagnostics.Process class in a Script Task to control the behaviour of the parallell execution. That way I can capture both the output and exact return value from dtexec. For each iteration of the loop I also save the Processes in a hash table, and I have another Script Task after the For Each loop where I iterate over the hash table and wait for the completion of everything that is parallelly executing. Regards, Lars

  Thank you Lars.

  It's a ray of hope for me

  So we need a For Each Loop Container with a Script Task inside it that calls the child package, that is it

  What is the hash table used for

  
  

• StephenWong

  Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:
  
  Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.
  
  What kind of executable files is the text referring to  Those that are called using the "Execute Process Task" within a package It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.
  
  Regards,
  Lars
  
  

• lexk

  I ended up using the System.Diagnostics.Process class in a Script Task to control the behaviour of the parallell execution. That way I can capture both the output and exact return value from dtexec. For each iteration of the loop I also save the Processes in a hash table, and I have another Script Task after the For Each loop where I iterate over the hash table and wait for the completion of everything that is parallelly executing.

  Regards,

  Lars

  
  
  

• RizwanSharp

  Code Snippet

  Imports System.Diagnostics

  Public Sub Main()
  ExecuteProcess("dtexec.exe", "SSIS package...")
  Dts.TaskResult = Dts.Results.Success
  End Sub

  Private Function ExecuteProcess(ByVal exe As String, _
  Optional ByVal args As String = "") As String

  
  Dim objProcess As New Process()
  objProcess.StartInfo.FileName = exe

  ' Any arguments that the program needs
  If args <> "" Then objProcess.StartInfo.Arguments = args

  objProcess.StartInfo.UseShellExecute = False
  objProcess.StartInfo.RedirectStandardOutput = True
  objProcess.StartInfo.CreateNoWindow = True
  objProcess.Start()

  ' Wait just for 1 second until the program to exit.
  ' objProcess.WaitForExit(1000)

  End Function

  Regards,

  Yitzhak

  
  

• Alsin

  Executables are tasks in a single package.  The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.
  
  Matt
  
  

• JeffKish

  lasa wrote:

  Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack Thanks, Lars

  
  
  The smart money says this will appear in a later version. Alot of people are asking for it.
  
  -Jamie
  
  
  

• hojjats

  3) You don't have any control over the degree of parallelism.  If you have 100 files, but only want to process three at a time for example.
  
  

• ducati1212

  Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack
  
  Thanks,
  Lars
  
  

• Aiwa

  Note that
  1) parent can't reliably get execution result from the children, since it may exit before all children finish,
  2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.
  
  

• Arshvinder Bhatia

  Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

  
  
  

• marco.ragogna

  ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.
  
  If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
  application="cmd.exe" and
  parameters="/c start dtexec.exe /f package file ..."
  
  You'll also need to configure child package using DTEXEC's command line (where I've left '...').
  
  

• fg_diverse

  Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished
   
  I am going to try this out and will report back.
  Regards,
  Lars

  
  
  
• Foreach loop with parallel execution