none
SSIS Conditional Flow?

    Question

  • Hi,

    In the new SSIS how can i implement a conditional workflow without having a base task?

    Imagine i have a FOREACH Loop based on a resultset and if a certain column have True i want to execute a certain task inside the loop and if is false i want to execute another one...

    How can i achieve this?

    Best Regards,
    Luis Simões
    Friday, December 02, 2005 4:19 PM

Answers

  • Precedence constraints are a more visible representation of what will happen at runtime. If you gave your package to someone else it wouldn't be immediately obvious to them that there is an expression on the "Disable" property, but an expression on a precedence constraint is signified by the colour of the constraint.

    Also, if you have an OnSuccess precedence contraint from a disabled task to an enabled task does the enabled task get executed or not? There is a right and a wrong answer to this question and you may be surprised at which one is correct.
    I'm not going to tell you the answer (you can easily find out for yourself) because I am trying to emphasize the ambiguous nature of disabling tasks at runtime. With precedence constraints there is no such ambiguity.

    -Jamie

    Saturday, December 03, 2005 10:00 AM

All replies

  • Put an empty sequence container in front of the tassk you want to execute and put an expression on the precedence contraint between them.

    -Jamie
    Friday, December 02, 2005 5:00 PM
  • Thanks but it's not needed Big Smile

    I have used expressions to disable one and enable another using two parallel tasks.

    Thanks anyway for the solution. Smile


    Best Regards,
    Luis Simões
    Friday, December 02, 2005 5:03 PM
  • Don't disable tasks at runtime - that's considered bad practice. The correct way to do this is via a precedence constraint.

    -Jamie
    Friday, December 02, 2005 5:27 PM
  • Why is that a bad practice? What are the consequences of using it? Isn't it more logical to have sequence containers enable or disabled based on boolean expressions?

    Best Regards,
    Luis Simoes
    Saturday, December 03, 2005 12:52 AM
  • Precedence constraints are a more visible representation of what will happen at runtime. If you gave your package to someone else it wouldn't be immediately obvious to them that there is an expression on the "Disable" property, but an expression on a precedence constraint is signified by the colour of the constraint.

    Also, if you have an OnSuccess precedence contraint from a disabled task to an enabled task does the enabled task get executed or not? There is a right and a wrong answer to this question and you may be surprised at which one is correct.
    I'm not going to tell you the answer (you can easily find out for yourself) because I am trying to emphasize the ambiguous nature of disabling tasks at runtime. With precedence constraints there is no such ambiguity.

    -Jamie

    Saturday, December 03, 2005 10:00 AM
  • Hello LUIS,
    
    LUIS, as jamie has pointed out disabling tasks in production is the wrong 
    way to do what you want here.  This is an old 2000 and 7 trick that is no 
    longer needed.  We talk about it here
    
    Meaning Of The On Success Workflow Constraint
    (http://www.sqlis.com/default.aspx?85)
    
    
    expressions on workflow is far far far (did I mention far) better way of 
    doing things.  Disabling tasks alters the structure of the package, is less 
    visible and requires turning on and off all the time.  It is as the article 
    says a design time thing.  Also note that just because you disable a task 
    does not mean the workflow stops there which may or may not be what you want. 
     Using expressions on the workflow means the flow will never get there period.
    
    Have a look here
    
    Introduction to Expressions on Workflow
    (http://www.sqlis.com/default.aspx?306)
    
    
    
    Allan
    
    
    > Why is that a bad practice? What are the consequences of using it?
    > Isn't it more logical to have sequence containers enable or disabled
    > based on boolean expressions?
    > 
    > Best Regards,
    > Luis Simoes
    
    
    

    Saturday, December 03, 2005 10:08 AM
  • Ok i understand that visually it is more effective.

    So the best way is to insert an empty script task and then insert conditional expressions in the various flows coming out of it?

    Best Regards,
    Luis Simões
    Sunday, December 04, 2005 4:26 PM
  •  Luis Simões wrote:
    Ok i understand that visually it is more effective.

    So the best way is to insert an empty script task and then insert conditional expressions in the various flows coming out of it?

    Best Regards,
    Luis Simões


    Almost. Use an empty sequence container instead of a script task.

    -Jamie
    Sunday, December 04, 2005 5:29 PM
  • OK Jamie ;)


    Thank you guys.

    I consider this task closed since the question is clear :)

    Best Regards,
    Sunday, December 04, 2005 5:32 PM
  • Sorry to reopen this thread but I have a similar problem and do not fully understand the solution.

    In my case I am checking to see if a file exists (using a Script Task). If the file exists, I set the task result to success and the package moves on to processing the file. If the file does NOT exist, I set the script task result to failure and I go to a File Watcher task. When the file becomes available it moves on to the same process file task.

    This solution works for the most part (executes all tasks) except the package itself fails when the file is not present. (Because my max errors was hit and I set the script task result to failure.)

    I am guessing there is a better way. Do I need to use some global variable (which would get set in the script task) and then use expressions? Which properties would I set? Would it be on the precedents themselves?

    In the posts it is mentioned to use empty sequence container. I do not quite understand how this would fit into the picture.
    Monday, April 24, 2006 5:22 PM
  • Jason,

    The empty sequence container is used when you have to make a choice about which task to execute PRIOR to any tasks executing. That's not relevant for your scenario.

     Jason Callas wrote:
    Do I need to use some global variable (which would get set in the script task) and then use expressions?

    Yes. Absolutely correct.

     Jason Callas wrote:

    Which properties would I set?

    You don't set any properties. You just have a boolean variable that you set to TRUE or FALSE accordingly from within the script task.

     Jason Callas wrote:

    Would it be on the precedents themselves?

    You place an expression onto the precedence constraints that evaluates whether to execute the next task based on the value of the variable. Here's how you set expressions on precedence constraints: http://www.sqlis.com/default.aspx?306

    -Jamie

     

     

    Monday, April 24, 2006 8:30 PM
  • Hi Guys,

    I too have struck this issue and have used the dummy sequence to facilitate conditional workflow.

    However, this really is a kludge, and is this going to be addressed in future versions of SSIS?

    Michael Morrissey.

    Tuesday, May 16, 2006 7:13 AM