1

I have 3 tasks in an SSIS package. The first task is an Execute Process Task that runs a PowerShell command to download a file from a website. If the command fails the task fails. So I have two connections from it to two other tasks: one for success and one for failure.

The package works as expected. If the Execute Process Task succeeds it runs the associated success task. If the fails it runs the associated failure task.

The problem is that if the Execute Process Task fails, it shows the task as failing even though it correctly/properly ran the failure task connected to it.

Is there anyway to get it to stop showing as failure but still run the associated failure task even when the Execute Process Task fails?

Hadi
  • 36,233
  • 13
  • 65
  • 124
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • 1
    Yes there is a way - you google it! http://datachix.com/2011/02/08/messing-with-errors-in-ssis-or-does-the-propagate-property-really-exist/ – Nick.Mc Feb 04 '18 at 04:31
  • @Nick.McDermaid i think the OP is asking to **remove the failure mark from the Execute Process Task**, and at the same time run the failure precedence constraint that is related to this task. It is a different case from `Foreach Loop container`. I don't think this can be done without a workaround – Hadi Feb 04 '18 at 09:29
  • 1
    In the OP's comment below he adds "the package as a whole registers as a failure" - which is of course is not mentioned in the original post and makes a different question. The idea in the link should be able to be used for any task... though I'm not sure. I do recall doing this a different way (not marking the packages as failed) although I can't remember – Nick.Mc Feb 04 '18 at 09:31
  • 1
    @Nick.McDermaid i think you're right, i updated my answer, just take a look – Hadi Feb 04 '18 at 09:40

2 Answers2

1

Update 1

If you need that the package doesn't result failure then you should set FailPackageOnFailure and FailParentOnFailure to False on the task you should also set MaximumErrorCount on the package itself to something greater than 1. If the Execute Process Task fails it will increment the package's error count and if the error count exceeds MaximumErrorCount then the package can/will still fail. Else if you only need that the task doesn't shows failure it cannot be done

SSIS -- Allow a task to fail but have the package succeed? (See all answers, not only the accepted one)

Initial Answer

You cannot use a failure precedence constraint if the precedent task will always succeed

I don't think you can do this. because the failure precedence constraint (connector) only works if the precedent task fail. You can use ForceExecutionResult property to let a task always success, but the failure connector will never be used.

Workaround

I am not sure if this can help, but instead of using failure precedence constraint, store the Execution Value in a Variable, using ExecValueVariable property, and add Expressions to the precedence constraints *(both connector will have the Success constraint and a similar Expression:

@[User::ResultValue] = 1

OR

@[User::ResultValue] = 0

Side Note: ExecValueVariable and ForceExecutionResult and other properties are found in the properties Tab, click on the Task and press F4 to show it

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    The task does fail. If the PowerShell command being called by the `Execute Process Task` fails then the flow does go through the `failure` precedence connector. That all works. The issue is the `Execute Process Task` also shows as failure so the package as a whole registers as a failure. I would think that if a task failure is handled with a failure precedence constraint then the task would still succeed. – IMTheNachoMan Feb 04 '18 at 03:30
  • @IMTheNachoMan if you need that the package doesn't result failure then you should set `FailPackageOnFailure` on the task you should also set `MaximumErrorCount` on the package itself to something greater than `1`. The task will still increment the packages error count and if the error count exceeds `MaximumErrorCount` then the package can/will still fail. Else if you only need that the task doesn't shows failure it cannot be done – Hadi Feb 04 '18 at 09:37
  • `FailPackageOnFailure` and `FailParentOnFailure` are both set to `False` but no effect. And changing the `MaximumErrorCount` wont help since the task will fail no matter how many times it is run. I would think if a task has a failure connection then that's like saying "fail gracefully" so the task wouldn't show as a failure. Poor design if you ask me. Thanks though! – IMTheNachoMan Feb 04 '18 at 14:44
  • @IMTheNachoMan what you are aking for cannot be done, my answer update is only if executing the package from an SQL agent job or application and you want htat the package execution return success instead of failure – Hadi Feb 04 '18 at 14:46
  • @IMTheNachoMan the only way you can do is **workarounds** – Hadi Feb 04 '18 at 14:47
0

So it seems that you want the task to be shown as success even though the parent task fails and the task corresponding to the failure output is successful. To do this you need to edit the field FailTaskReturnCodeIsNotSuccessValue to False so that even though the parent task doesn't return the successValue the task is not shown as failed.Hope this helps!!.

Ajitkr
  • 1
  • I want the task to fail and route to the failure connection (which it does) but I don't want the task to show as failure because that fails the entire package. – IMTheNachoMan Feb 05 '18 at 02:00
  • @IMTheNachoMan set `MaximumErrorCount` > the number of errors thrown by Execute Process Task – Yahfoufi Feb 05 '18 at 10:40
  • @Yahfoufi The `Execute Process Task` will either succeed or fail every time it is called. All it does is try to download a file using PowerShell. If the file is not there then it'll fail. – IMTheNachoMan Feb 05 '18 at 14:21