HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?

Submitted by: @import:stackexchange-dba··
0
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?

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.

  • 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.