Post reply

SSIS Failure Workflow not getting triggered upon OLAP processing task failure

  • March 23, 2017 at 8:18 am

    #323031

    Hi,

    I have a SSIS package which contains a For Loop container that will execute at max 100 times. Within this container is an OLAP processing task that processes one single dimension. If the OLAP task fails, there is a Failure workflow that should be triggered which should result in the execution of an Expression task. However, when the OLAP task fails, the Failure workflow doesn't seem to get triggered and so the Expression task never executes. The For loop just goes on to the next iteration.  Does anyone have any idea why the Failure Workflow isn't getting triggered when the OLAP tasks fails?  Any help would be appreciated.

    Thanks! 

  • March 28, 2017 at 3:03 am

    #1935218

    Does the Error workflow attach from the OLAP Task or the For Loop? If it attaches from the For Loop  make sure Fail Parent is set in the properties of the OLAP Task.

  • March 28, 2017 at 10:23 am

    #1935314

    Hi Tim,

    Thanks for the reply. The Error/Failure workflow attaches from the OLAP task to an Expression task and both of these tasks are within the For Loop container. Thanks

  • March 28, 2017 at 11:40 am

    #1935340

    Edding Jan - Tuesday, March 28, 2017 10:23 AM

    Hi Tim,

    Thanks for the reply. The Error/Failure workflow attaches from the OLAP task to an Expression task and both of these tasks are within the For Loop container. Thanks

    Then as he indicated, you'll need to set a property value on the OLAP task, named FailParent to True.   Click on the OLAP task, then look at the properties area on the screen, or if it's not displayed, instead, right click on the OLAP task and choose Properties.   Scroll up or down until you find the FailParent property and change the value to True.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • March 28, 2017 at 11:49 am

    #1935345

    Ok, thanks Steve.  I will give that a try.  But I had thought Tim indicated that I was to set the Fail Parent for the OLAP Task only if the Error/Failure workflow attaches to the for loop: "If it attaches from the For Loop make sure Fail Parent is set in the properties of the OLAP task."   I didn't think this would apply to my situation since the Failure workflow is not attached to the For Loop.  Maybe I'm misunderstanding.

  • March 28, 2017 at 12:15 pm

    #1935358

    Edding Jan - Tuesday, March 28, 2017 11:49 AM

    Ok, thanks Steve.  I will give that a try.  But I had thought Tim indicated that I was to set the Fail Parent for the OLAP Task only if the Error/Failure workflow attaches to the for loop: "If it attaches from the For Loop make sure Fail Parent is set in the properties of the OLAP task."   I didn't think this would apply to my situation since the Failure workflow is not attached to the For Loop.  Maybe I'm misunderstanding.

    Then the question is, what IS attached to the Failure workflow?   You have to be able to "bubble up" the failure until it reaches the necessary level.   This may mean that even the ForEach Loop Container may need this same property set so that it's parent will fail when it does, having already set the OLAP task FailParent property to True.   And if there are levels between the ForEach Loop Container and the Failure workflow, each of those levels may need to have this property set.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • March 28, 2017 at 12:33 pm

    #1935363

    The Failure workflow attaches from the OLAP task to an Expression task which both reside in the For Loop.   Please see the image below. 
    Just to reiterate, all I'm wondering about is why the Expression Task doesn't seem to execute when the Analysis services Dimension fails.
    Thankfully, this isn't too critical for me to figure out at the moment. But this was just something that was confusing me.

    ad.

  • March 29, 2017 at 2:24 am

    #1935439

    It looks like you have a bit of a mystery on you hands. As all of the tasks are contained in the For Loop setting FailParent will not help in this case.

  • March 29, 2017 at 12:03 pm

    #1935557

    Edding Jan - Tuesday, March 28, 2017 12:33 PM

    The Failure workflow attaches from the OLAP task to an Expression task which both reside in the For Loop.   Please see the image below. 
    Just to reiterate, all I'm wondering about is why the Expression Task doesn't seem to execute when the Analysis services Dimension fails.
    Thankfully, this isn't too critical for me to figure out at the moment. But this was just something that was confusing me.

    ad.

    It looks to me like you've simply set the condition for the path to the next task after the Analysis services Dimension to operate on failure instead of on success.   That doesn't make any sense, unless it's now the Expression Task that is failing (the red circle that I'm seeing suggested that).   The property to show a picture of would still be FailParent, and it's in the properties window.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • March 30, 2017 at 8:00 am

    #1935726

    Hi Steve,

    "It looks to me like you've simply set the condition for the path to the next task after the Analysis services Dimension to operate on failure instead of on success. "

    Yes, I want the Expression task to execute if the OLAP task fails.

    "unless it's now the Expression Task that is failing (the red circle that I'm seeing suggested that)."

    Sorry for the confusion, but the red circle doesn't imply the task failed.  It simply indicates that there is a breakpoint assigned to one of the events associated with the task.  I believe I had added to breakpoint in hopes of seeing where the task might be executing and then possibly prematurely terminating. But task didn't seem to get executed at all as the process wasn't getting stopped at the breakpoint.

    What I should have mentioned is that I set the Propagate system property to False in the OnError event for the OLAP task to ensure that the error would not bubble up to the Loop container. I don't know if that would cause the Failure workflow that is attached to the OLAP task from not executing. 

    Thanks for getting back to me, but I think I need to take a break from this thread for a bit as there are other things I need to work on at the moment.

  • April 3, 2017 at 2:32 pm

    #1936418

    Edding Jan - Thursday, March 30, 2017 8:00 AM

    Hi Steve,

    "It looks to me like you've simply set the condition for the path to the next task after the Analysis services Dimension to operate on failure instead of on success. "

    Yes, I want the Expression task to execute if the OLAP task fails.

    "unless it's now the Expression Task that is failing (the red circle that I'm seeing suggested that)."

    Sorry for the confusion, but the red circle doesn't imply the task failed.  It simply indicates that there is a breakpoint assigned to one of the events associated with the task.  I believe I had added to breakpoint in hopes of seeing where the task might be executing and then possibly prematurely terminating. But task didn't seem to get executed at all as the process wasn't getting stopped at the breakpoint.

    What I should have mentioned is that I set the Propagate system property to False in the OnError event for the OLAP task to ensure that the error would not bubble up to the Loop container. I don't know if that would cause the Failure workflow that is attached to the OLAP task from not executing. 

    Thanks for getting back to me, but I think I need to take a break from this thread for a bit as there are other things I need to work on at the moment.

    Sorry to not have seen this post sooner, but the reason I asked the questions I did was because with the ExpressionTask in that picture would ONLY run if the OLAP Task fails, and thus the Script Task also never runs unless the Expression Task does.   I had forgotten about break points.   As to setting the propagate thing, I would wonder if you'd then actually see the OLAP task as a failure.   I was under the impression that doing such "impersonates success".   It's been a while now, so maybe this is a good time to re-examine things...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

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

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