Post reply

SSIS 2005 - Execution stops at any task with multiple prec constraints

  • November 3, 2009 at 12:53 pm

    #134961

    I have a package set up to perform some automatic flat file generation from some DB table data.

    There are a couple of conditions I check for prior to entering the file generation process, each may cause a branch to a task that logs some detail to a SQL table. The steps then fall through to another task that sends out an email summarizing info from the SQL log table. Call it the email task.

    Pretty simple stuff. All the pathways work fine if they are the only ones with a precedence constraint to the email task. If I have more than one constraint path leading to the email task, the process goes green until that task, then stops. The Progress tab shows it as completed.

    If I remove the "other" constraints leading to the email task (there are three possible paths to get there), it works fine. i.e. each individual pathway works fine from beginning to end.

    BTW, I call it the email task, but I tried putting in an intervening task (empty script task) upstream from it just to allow the paths to join together, with the same results.

    Any ideas on this one?

    Tnx

  • November 3, 2009 at 2:57 pm

    #1074553

    Did some more testing and have a simple example of the issue I am seeing:

    Create a package. Add a SQL task that performs "Select 1" and stores the 1 to a package variable var_1.

    Then have two constraints coming from this task, based on the evaluation of var_1. In my example I have them going to two separate script tasks (with no content).

    Then have the precedent constraints from these two tasks fall through to a common final task (again I used an empty script task).

    So we have a task with two conditional pathways, that then rejoin. If you run this, it will run without error, but stops before the task that reunites the two pathways.

    This illustrates my development issue, where I have several data tests requiring logging to SQL table, then a common task to read the table and email the results. Each test has a conditional branch to a logging step, and all logging steps reunite in an email step.

    If only one pathway (I have tried all) exists, all works well. If there are two pathways that have an expression evaluation on them upstream, the task with multiple entry paths seems to stop the process dead.

  • November 3, 2009 at 3:09 pm

    #1074561

    Each task in the control flow waits for the previous task to complete. If you have 2 precedence constraints leading into a task, it will wait for both to complete before it runs. It can't see that you've made a decision higher up to only execute down one logical path so it waits for both sides of the decision path to complete.

    Instead of using multiple precedence constraints, put all of the tasks prior to the e-mail inside a sequence container and run 1 precedence constraint from the sequence container to the email task.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 3, 2009 at 3:30 pm

    #1074578

    John Rowan (11/3/2009)

    Each task in the control flow waits for the previous task to complete. If you have 2 precedence constraints leading into a task, it will wait for both to complete before it runs. It can't see that you've made a decision higher up to only execute down one logical path so it waits for both sides of the decision path to complete.

    Instead of using multiple precedence constraints, put all of the tasks prior to the e-mail inside a sequence container and run 1 precedence constraint from the sequence container to the email task.

    I concur, but I would also suggest that you could put the email task in a sequence container and see if that works as well.

    I have an SSIS package with 28 parallel sequence containers that all lead back to a single sequence container where I compress/archive 28 files then FTP the archive file.

  • November 3, 2009 at 3:37 pm

    #1074582

    Thanks folks. I am now finding some details that indicate I can change the precedence constraint to include the OR ... expression. In small tests this seems to have the desired effect.

    Lynn, can I assume that in your package with 28 parallel processes, the branching is not conditional to the processes and then back to a common path?

    Tnx

  • November 3, 2009 at 3:38 pm

    #1074584

    True, which is why I would actually try the other way first.

  • November 4, 2009 at 9:15 am

    #1074870

    Thanks John and Lynn. I am bundling my tasks into sequence containers appropriately and things appear to be working fine now that I understand the underlying issue.

    Feedback most appreciated!

    Gary

  • November 4, 2009 at 9:57 am

    #1074900

    Glad to here things are working now.

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

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