Add conditions to maintenance plan sequence on SQL Server

Question

I would like to add a condition to a sequence in my SQL Server 2008 Maintenance Plan. This condition is based on the variable set by a T-SQL Statement Task:

declare @primary bit = 0
select @primary=1
from sys.database_mirroring
where mirroring_role = 1

How can I perform this task through the precedence constraint editor?

Answer 1

Honestly if you're going to get into advanced tweaking like that you're better off scripting a solution and then scheduling it via Agent. Maint Plans, while nice for really simple tasks, really has its limitations.

Answer 2

I agree with SQLChicken. You won't be able to check the variables via a simple maintenance plan you create in SQL Management Studio. You will need to either script it out and put it in a SQL job or create a full-on SSIS package. SSIS will provide you with all of the maintenance plan tasks that are available via SSMS as well as all of the other benefits of SSIS including package variables that you can use in your precedence constraints.

Answer 3

I have a solution to this however it is not elegant. This works, but it is a cludge and it would never be considered a "best practice".

The solution involves using artificially generated errors to control flow, and tweaking package properties so that the overall error condition of the calling job reports success or failure the way we would want.

First, create an Execute T-SQL Statement task with your conditional check. From the example in the original question, this would look like this:

if not exists (select * from sys.database_mirroring where mirroring_role = 1)
   begin
   raiserror('not primary', 16, 1)
   end

This code generates an error if this is not the mirroring primary, and no error if it is the primary. Next, create second Execute T-SQL Statement task and link it to the first one with a Success condition. If this is not the mirror primary we will not go to this second task, and the sequence will end. If this is the primary we will proceed to this second task. The second task generates a dummy error using code like this:

raiserror('dummy error', 16, 1)

Now create your third task and link it to the second with a Failure condition. In this third task, do whatever it is you wanted to do if the condition in the first task was true. This can be another Execute T-SQL Statement, a Backup task, Update Statistics task, or whatever. If the condition was false, we will leave the sequence after the first task. If the condition was true we will progress through the dummy error and on to this third task which actually does the work we want done.

The reason for the dummy error in task 2 is to make the final error state of the calling job report what we want it to, and there are some other package properties we also need to tweak to make this work. Go to the properties window (if it isn't open, open it from the right click menu on any one of the tasks) and click on the dropdown at the top. This lists all the maintenance plan elements that you can change properties for. Click on Subplan_1 Sequence where Subplan_1 is the name of the subplan you are working in. Change FailParentOnFailure to False. This will keep the calling job from reporting an error when we generate the conditional error in task 1 or the dummy error in task 2. Next, go to properties for MyPackage Package where MyPackage is the name of the package you are working in. Change the MaximumErrorCount to 2. This will cause the calling job to report success when there is only one error generated by the dummy error in task 2, but report failure if the third task also generates an error. It will also report success if the only error generated is by the conditional check in task 1.

That's it, I hope someone finds this useful.

Answer 4

For me the easiest way for conditional maintenance plans without installing Information Services on your SQL instance is to use SQL Server Data Tools (SSDT):

1. Download installation of the SSDT from here (it is free): https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
2. Create a new SSIS project. One of the files the new project has is Package.dtsx It is a XML file.
3. Select from you database the XML for you maintenance plan with following query:

SELECT    id, name, description, CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) xml_str
FROM  msdb.dbo.sysssispackages with(nolock)
WHERE name='your maintenance plan name';

4. Copy-paste the xml_str column value into the Package.dtsx file of the SSIS project you have created.
5. Add variables and add ‘Execute SQL Task’ node. The ‘Execute SQL Task’ allows you to retrieve data with selects into the variables unlike ‘Execute T-SQL Statement task’ that SQL Server maintenance plan have. Once inserted into SQL maintenance plan the ‘Execute SQL Task’ has GUI to be edited directly into the SSMS without SSDT. But variables may only be added and edited with the SSDT or with SQL that directly alters the XML of the maintenance plan. Don’t forget to save.
6. Copy as text into the clipboard the XML from the Package.dtsx file and update with SQL command the maintenance plan packagedata field of the msdb.dbo.sysssispackages table. Now if you open the maintenance plan with the SSMS you will find out that added ‘Execute SQL Task’ has a GUI and is perfectly configurable even from SSMS.
7. After the ‘Execute SQL Task’ that will gather needed information into variables create constraints/links with expression. The constraints/links must connect nodes that should be executed conditionally. The expressions will instruct the flow which constraint/link to be followed and which not. The expressions are utilizing the variables data for the conditions. That can be done through the SSMS GUI without additional tools.

Of course all of that is achievable, without SSDT, only with pure update SQL commands if you know exactly what XML must be amended for variables and the for the ‘Execute SQL Task’ into the package data XML of the target maintenance plan. Once inserted in one maintenance plan the ‘Execute SQL Task’ node may be copy and pasted into other maintenance plans through the SSMS GUI without additional tools. Unfortunately that is not the case with the variables.