snippetsqlMinor
How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?
Viewed 0 times
subtaskprecedingsuccesssqlplanmaintenanceunconditionallytaskshowserver
Problem
I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio Maintenance Plan Wizard.
AFAIU, the penultimate task "Back Up Database Task" will be executed only on the success of all preceding tasks. Since the constraints (arrows containing tasks) are all the same (taken from default options creation):
and use "Logical AND"
Now if I change in the preceding constraint the "Logical AND" to "Logical OR", the backup task still will fail if one of the preceding tasks fail.
Correct?
How can I make the backup task to run in any case?
UPDATE
After accepting the answers, I am still in doubt about the term "completion":
Does failed task considered to be run to completion in SQL Server maintenance plan terminology?
AFAIU, the penultimate task "Back Up Database Task" will be executed only on the success of all preceding tasks. Since the constraints (arrows containing tasks) are all the same (taken from default options creation):
and use "Logical AND"
Now if I change in the preceding constraint the "Logical AND" to "Logical OR", the backup task still will fail if one of the preceding tasks fail.
Correct?
How can I make the backup task to run in any case?
UPDATE
After accepting the answers, I am still in doubt about the term "completion":
Does failed task considered to be run to completion in SQL Server maintenance plan terminology?
Solution
There are 3 options in Value: Success, Failure and Completion (approximate). Changing the value to Completion for all the preceding precedence constraints should ensure that the backup task is reached regardless of the upstream tasks.
Execution Results
The precedence constraint can use the following execution results alone or in combination with an expression.
Further reading:
Execution Results
The precedence constraint can use the following execution results alone or in combination with an expression.
- Completion requires only that the precedence executable has completed, without regard to outcome, in order for the constrained executable to run.
- Success requires that the precedence executable must complete successfully for the constrained executable to run.
- Failure requires that the precedence executable fail for the constrained executable to run.
Further reading:
- https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/
- http://technet.microsoft.com/en-us/library/ms141261.aspx
- http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/11/things-you-might-not-know-about-precedence-constraints-ssis.aspx
- Link
Context
StackExchange Database Administrators Q#50400, answer score: 5
Revisions (0)
No revisions yet.