SQL Server 2005 Integration Services – Maintenance Plan Tasks – Part 47

In the previous
of our series dedicated to SQL Server 2005 Integration Services,
we introduced the concept of Maintenance Plans and presented a brief overview
of three methods that can be used for their implementation. We also described
in more detail the most straightforward of them, which is initiated from Object
Explorer of SQL Server Management Studio and configured through a series of
consecutive, wizard-driven pages. In addition, we mentioned that a variety of
features (along with representing them graphical interface options) have
changed with the advent of SQL Server 2005 Service Pack 2. We will point out
these changes as we continue our presentation, in order to bring your attention
to some of the more relevant improvements (for a complete list of new features
in SQL Server 2005 SP2, refer to the "What’s New
in SQL Server 2005 SP2"
article of SQL Server 2005 Books Online posted
on the MSDN Web site). On the other hand, remember that the initial release of
the service pack contained a number of software bugs, which had a negative
impact on maintenance plan functionality. To ensure that these issues will not impact
your environment, you should verify that you have installed the corrected
version (which was made available by Microsoft on March 5 2007) as well as
apply the instructions outlined in Microsoft Knowledge Base
Article 933508

As we have demonstrated in the previous article, successful completion of
the Maintenance Plan Wizard generates a new SSIS package that is saved in the msdb
database. This event coincides with the creation of one or more SQL Server
Agent jobs of Database Maintenance type (appearing in the Jobs subfolder of the
SQL Server Agent node of Object Explorer in SQL Server Management Studio).
Their actual count depends on the number of unique execution options you
selected when running the wizard. This number also determines how many subplans
within the Maintenance Plan package you have (subplans serve a role similar to
SSIS Sequence Containers, controlling the sequence of task execution within a
package; however, unlike containers, they can be individually scheduled). If
you decided to apply the same schedule to all tasks or to use on-demand
invocation, you will end up with a single job entry (and a single subplan).
Choosing a separate schedule for all tasks will result in an individual job for
each of them (each corresponding to a distinct subplan). Subplans auto
generated by the wizard are named simply as Subplan_1, Subplan_2,
etc. Names of jobs are formed by concatenating identifiers of the maintenance
plan and their matching subplans, with a single period between them (e.g. MaintenancePlan_1.Subplan_1).

In order to edit (or simply view) the content of a maintenance plan, use the
Modify option from its context sensitive menu (all plans are listed in the
Maintenance Plans subfolder of the Management folder in Object Explorer of SQL
Server Management Studio). The appearance of the resulting designer window
depends on the service pack level. With SQL Server 2005 SP2, the design area is
divided into three, horizontally arranged sections. The top one contains the
name and description of the currently viewed maintenance plan. Immediately
below, there is a listing of all of its subplans, with their description and
schedule (two buttons on the right hand side allow you to, respectively,
configure and delete scheduling information). Selecting an individual subplan
from this list displays its graphical representation in the third section, in
the lower portion of the designer window. From here, you can manipulate
properties of existing maintenance plan tasks, add new ones by dragging them
from the Toolbox, or set precedence constraints between them. The toolbar at
the top of the designer window gives you the ability to add new subplans (in
case you want to introduce tasks with their own, independent schedules), modify
or delete existing ones, configure reporting and logging options (that apply to
all subplans within the maintenance plan), as well as manage server connections
(including their authentication methods). Using Servers… toolbar button, you
can also designate servers where the maintenance plan subtasks will execute
(providing you operate in a multiserver environment with target servers running
jobs defined on a master).

All of the tasks that are listed in the Toolbox of Maintenance Plan designer
in SQL Server Management Studio have their own specific editors, which are
identical to the ones available in the SSIS Designer window of Business
Intelligence Development Studio. One of quite useful features common to all
editors is the View T-SQL command button, which allows you to display T-SQL
statements that produce the same outcome as applying properties you set through
the graphical interface. As we will demonstrate later in our series, this helps
address some of the shortcomings of current Maintenance Plan implementation.
Furthermore, you can use these statements to implement maintenance procedures
in SQL Server 2005 Express edition, which does not support directly SSIS or
Maintenance Plans. (Although, keep in mind that automating them would also
require some type of scheduling software, such as Windows Task Scheduler, in
order to compensate for the lack of SQL Server Agent Job Scheduling service

Interestingly, some of subplan settings can be configured from either the
Maintenance Plan designer window (displayed after you select the Modify entry
from the context sensitive menu of the plan you want to modify that appears in
the Maintenance Plans subfolder of Management folder in Object Explorer of SQL
Server Management Studio) or from the Properties dialog box of a job that
correspond to this subplan. (To activate this dialog box, select the Properties
entry from the context sensitive menu of this job, which is listed under the Jobs
subfolder of the SQL Server Agent node in Object Explorer of SQL Server
Management Studio). For example, this is the case with schedules of individual
subplans, which are identical to corresponding job schedules and can be
accessed either via the Schedules section of SQL Server Agent Job Properties
dialog box or via the Subplan schedule command button in Maintenance Plan
designer (either approach triggers a display of the same Job Schedule
Properties dialog box). On the other hand, there are also some seemingly
partially redundant settings, which serve a similar role but deliver them at
different stages of the maintenance plan (and corresponding job) execution. For
example, within Maintenance Plan designer window, you can configure
notifications by introducing Notify Operator Task into the workflow area (which
provides status information about intermediate stages of execution via an
e-mail to designated operators), or through the Reporting and Logging dialog
box (from which you can specify recipients of e-mail reports on the outcome of
the maintenance plan). Note that both mechanisms require Database Mail functionality.
Another similar option, which appears in the Notifications section in the Job
Properties window for a SQL Server Agent job, is intended for defining actions
that are performed once the job completes. These actions include sending pages
and e-mails (which also rely on Database Mail being properly set up), NET SEND
messages (which require Messenger service to be running on the source and
target systems), recording relevant information in the Windows Application log,
as well as automatically deleting jobs that have completed. You can turn on any
of them conditionally, depending on the job success, failure, or simply

There are also several SSIS specific parameters that are configurable from the
SQL Server Management Studio interface. In order to review them, double-click
on a job you want to edit listed under the Jobs subfolder of SQL Server Agent
node in Object Explorer of SQL Server Management Studio. In the resulting Job
Properties dialog box, switch to the Steps section (listed on the left side)
and click on Edit after selecting a relevant Subplan entry. This will display the
Job Step Properties dialog box. From here, you should be able to view and edit
such settings as package source, target server and authentication method, configurations,
Command Files, Data sources, Execution options, Verification, or Logging – just
to mention a few.

Once you have adjusted configuration settings according to your
requirements, you might attempt to launch your maintenance plans.
Unfortunately, even this seemingly straightforward activity has a significant
number of caveats you need to be aware of. If you are planning to schedule
execution of individual subplans within a single maintenance plan (which is
equivalent to scheduling a SQL Server Agent job), then you should take into
consideration items outlined in Microsoft
KB Article 918760
titled "An SSIS package does not run when you call
the SSIS package from a SQL Server Agent job step". On the other hand, if
your intention is to launch subplans interactively, you can accomplish this
with the Start Job as Step… option in the context sensitive menu of jobs
listed under the Jobs subfolder of the SQL Server Agent node. In order to run
all subplans within a maintenance plan, you need to repeat this step for each
of them, since it appears that executing multi-subplan maintenance plans by
invoking the Execute option from their context sensitive menu (plans are listed
under the Maintenance Plans subfolder of Management folder in Object Explorer)
is not possible (although you can use this option when dealing with maintenance
plans that contain only a single subplan). Note that this surprising limitation
does not surface if you schedule maintenance tasks (rather than using them in
on-demand fashion), since subplans are launched separately as distinct SQL
Server Agent jobs.

Results of the execution can be either communicated directly to designated
individuals (through the notification mechanism, which we described earlier) or
analyzed by reviewing the content of various logs. It is possible to enable
logging by leveraging SSIS providers (through the Logging tab on the General
page of the Job Step Properties dialog box), Windows Application log (from the
Notifications page of the Job Properties dialog box), or Log File Viewer, which
contains a comprehensive view of Database Mail, Job History, Maintenance Plans,
and SQL Agent logs (to display it, select the View History entry from the context
sensitive menu of the maintenance plan you are interested in, listed under the Maintenance
Plans subfolder of the Management folder in Object Explorer of SQL Server
Management Studio). As mentioned before, you can also enable logging via the
Reporting and Logging dialog box invoked with the toolbar button in the
Maintenance Plan designer window.

We will take a closer look at the remaining options available from this
window (including all of the maintenance tasks listed in its Toolbox) in the
next article of our series.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles