Add conditions to maintenance plan sequence on SQL Server

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.

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.

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.