Using SQL Server Management Studio:

• In Object Explorer, click to expand the server. Then click to expand Management folder.
• Right click the Maintenance Plans folder and select Maintenance Plan Wizard to use the wizard or select New Maintenance Plan to use the design surface.

• If you choose New Maintenance Plan, choose a name for the plan then click OK.

• This opens the toolbox with a default subplan.

• Double click on the default subplan and enter the following information. By default the job will run on demand. Configure scheduling.

• To build the subplan, drag and drop task flow elements from the Toolbox to the plan design surface. Double-click tasks to open dialog boxes to configure the task options.

• To define a workflow between tasks, Right-click the precedent task and select Add Precedence Constraint. In the Control Flow dialog box, in the To list, select the dependent task and click OK.

• Double click the connector between the two tasks to open the Precedence Constraint Editor dialog box.

Constraint option
Defines how a constraint works between two tasks.

Evaluation operation list
Specify the evaluation operation that the precedence constraint uses. The operations are: Constraint, Expression, Expression and Constraint, and Expression or Constraint.

Value list
Specify the constraint value: Success, Failure, or Completion. Success is the default.

(The precedence constraint line is green for Success, red for Failure, and blue for Completion.)

Test

Validate the expression.

Multiple constraints

Define how multiple constraints interoperate to control the execution of the constrained task.

Logical AND

Select to specify that multiple precedence constraints on the same executable must be evaluated together. All constraints must evaluate to True. This option is the default.

Logical OR
Select to specify that multiple precedence constraints on the same executable must be evaluated together. At least one constraint must evaluate to True.

• To add another subplan that contains tasks run on a different schedule, click Add Subplan on the toolbar to open the Subplan Properties dialog box.

 

• To add connections to different servers, In the design space’s toolbar, click Manage Connections. In the Manage Connections dialog box, click Add. In the Connection Properties dialog box, in the Connection name box, enter the name of the connection you are creating.

Under Specify the following to connect to SQL Server data, in the Select or enter a server name box, either enter the name of the SQL server you want to use or click the ellipsis (…) and select a server in the SQL Server dialog box. If you select a server from the SQL Server dialog box, click OK.

Under Enter information to log on to the server, select either Use Windows NT Integrated security or Use a specific user name and password. If you elect to use a specific user name and password, enter that information in the User name and Password boxes, respectively.

• To specify reporting options, In the design space’s toolbar, click Reporting and Logging. In the Reporting and Logging dialog box, under Reporting, select Generate a text file report or Send report to an email recipient or both.