Post reply

Wait for each iteration to complete in a For Each Loop Container

  • September 17, 2014 at 4:52 am

    #293322

    Hi,

    I'm not the most experienced person when it comes to SSIS, I hope someone can help me with this issue.

    I have a For Each Loop container that obtains a list of child packages to run in a set order determined by a control table, I have an Execute SQL Task that simply passes the list of all tasks into an Object variable called @[User::PackageTasks], then the For Each Loop container iterates through these and passes them one at a time into a string variable called @[User::PackageTaskName] which contains only one task at a time.

    What I need to do is iterate through each one of these tasks but wait until each one has completed before running the next package.

    I have tried using an Execute Process Task calling Dtexec and the individual DTSX files, but the problem with this is there is no waiting between each one to finish before the next one starts without some other means which I'm not sure on.

    The obvious method is to use the Execute Package Task but not sure how to pass the variable @[User::PackageTaskName] into the PackageNameFromProjectReference so that the Execute Package Task gets each Package Task name one at a time and then run the child package.

    I have added an expression PackageName and mapped variable @[User::PackageTaskName] but I cannot click OK, if I add a default value to the variable it allows me to OK the Execute Package Task editor but then states "Failed to locate the specified package in the project" and just fails.

    I will also then need to bring back a Run Number count from the child package to the parent so I know which attempt has been made to run. At the moment the RunNumber is only working for the master package.

    I hope someone can help with this as it is frustrating me.

  • May 10, 2017 at 10:21 am

    #1942049

    Hi Andrew,

    Lets do this with an example ....
    consider u have 3 packages to execute.
    Jobid         Jobname
    1               Abc
    2               Bcd
    3               Xyz

    and i assume that u have already loaded these details into any object variable ; lets say @package List

    step 1 : use for each loop container
    stpe 2 : decode the @package List Variable 
    step 3 : put execute package task inside container
    step 4 : inside execute package task , in expression use @User::Var_JobName+"dtsx"

    and the main thing because of u r getting package name validation error.
    step 1 : let say u have created a variable @Var_JobName string type .
    step 2 : give a default value as your first package name which u want to execute first.(this should from the current project list which u have created) .

    this will remove the validation error which u r getting.

    Let me know if u need any other further clarification

    Thanks
    Gaurav.

Viewing 2 posts - 1 through 2 (of 2 total)

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