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

In the most recent articles of our series covering SQL Server 2005
Integration Services, we have focused on maintenance plans activities,
represented by a number of tasks in Maintenance Plan designer of SQL Server
Management Studio (including additional caveats introduced by Service Pack 2
installation). As we have demonstrated, an identical set of tasks is also
available in the Control Flow area of SSIS Designer in Business Intelligence
Development Studio. Despite apparent similarities in the way these tasks are
presented in both interfaces, it is important to note that there are some
fairly significant differences between them (which reflect not only their unique
internal characteristics, but extend also to such mechanisms as maintenance
plan storage, scheduling, and execution). Today, we will review these
differences, by examining compatibility and level of integration between these
two alternative approaches to automating routine administrative
responsibilities.

As we have mentioned, both methods provide access to the same number of
maintenance tasks in a very similar manner (the third method we have described,
which involves running Maintenance Plan Wizard, is considerably more
restrictive, both in terms of interface and task selection). However, each also
has its distinct benefits and shortcomings. Business Intelligence Development
Studio is capable of addressing more complex and demanding scenarios through
its support for a wide variety of additional predefined and highly configurable
tasks. With its programming and debugging capabilities, it constitutes a
full-fledged development environment, capable of accommodating an extensive
amount of custom code. SQL Server 2005 Management Studio is intended primarily
as an administrative utility for managing daily server and database operations,
and, as such, does not offer the same level of sophistication as far as
programming options are concerned. On the other hand, its Maintenance Plan
designer, featuring subplans and built-in scheduling, can greatly simplify
implementation of most common maintenance scenarios (within Business
Intelligence Development Studio, you are limited strictly to interactive SSIS
package execution).

In order to familiarize yourself with Maintenance Tasks available in SSIS
designer, launch a Business Intelligence Development Studio and create a new
project of Integration Services type. Once the Control Flow area is displayed,
activate the Toolbox window and scroll to its Maintenance Plan Tasks section.
Its content is identical to the one we are already familiar from our earlier
review of Maintenance Plan Designer in SQL Server Management Studio. Note,
however, that the ability to define subplans and assign to each of them
individual schedules is missing. As a matter of fact, any SSIS package can only
be executed as a single unit of work, without the same level of scheduling
granularity that is possible in maintenance plans defined within SQL Server
Management Studio (where each plan can consist of multiple, individually
initiated subplans). Furthermore, SSIS packages that perform maintenance tasks
are not automatically encapsulated into SQL Server Agent jobs (unlike the ones created
with Maintenance Plan Wizard or Designer, which result in a separate job autogenerated
for each of its subplans). Another significant factor that differentiates
standard SSIS packages from Maintenance Plans we discussed previously is their
storage mechanism. SSIS Designer uses for this purpose DTSX files (which
coexist with a number of others, residing in the same directory structure
designated for each SSIS project). Packages created with Maintenance Plan
designer (and Maintenance Plan Wizard) are located in a number of associated
tables in the msdb database (for more information on these differences, refer
to our
earlier article
of this series). While it is possible to transfer a package
between file and database formats using import and export functionality in SQL
Server Management Studio (Import Package… and Export Package… options
appear in context sensitive menus of entries under the Maintenance Plans
subfolder of Stored PackageMSDB folder structure in the Integration Services
node of Object Explorer in SQL Server Management Studio), this does not mean
that they are compatible or can be converted without some of their original
functionality being lost.

To demonstrate this, let’s create a sample maintenance plan in SSIS Designer
and review its content from within SQL Server Management Studio (after
importing it into msdb database). Within the project you just initiated using
Business Intelligence Development Studio, drag Back Up Database Task onto the
Control Flow area of the SSIS Designer and activate its Editor window. Create
an ADO.NET connection pointing to your SQL Server, set the backup type to Full,
specify master as its target, and point either to a target device or a folder
where backup files will be stored. Once you have confirmed your choices and
returned to the Designer interface, add Maintenance Cleanup task to the
workflow and assign some arbitrary values to its options within its Editor
window. Connect both tasks with precedence constraint, making invocation of the
second dependent on the success of the first. Save the package and execute it
in order to verify that it operates properly. Once you have finished these
steps, launch SQL Server Management Studio and connect to both Database Engine
and Integration Services on the target server. Note that the package you just
created does not appear under the Stored Packages folder of the latter. In
order to remediate this, right-click on the Maintenance Plans subfolder (under
Stored Packages/MSDB folder structure) and select the Import package… option
from its context sensitive menu. In the resulting Import Package dialog box,
pick File System entry in the Package location listbox, specify the path
pointing to the DTSX file you created in the previous step, and assign a name
via which the package will be referenced in msdb database (you should also
select an appropriate Protection level setting, after carefully considering the
security implications of your choice – for more information on this subject,
refer to our
earlier article
). Once you click on the OK button in the dialog box, the
package entry will show up in the Object Explorer window in the location you
specified. At this point, you can export it to another location, configure its roles,
or interactively launch its execution. If your intention is to run the package
on a scheduled basis, you need to manually create a new SQL Server job (using
New Job… entry in the context sensitive menu of the Jobs folder under the SQL
Server Agent node of the Object Explorer) with a step of "SQL Server
Integration Services Package" type, point to the package location (note
that to accomplish this, it is not necessary to import the package into msdb,
but it is also possible to simply use the original DTSX file), and configure
relevant parameters (such as security context in which the step will be
running, alerts, notifications, or schedule).

Interestingly, even after importing an SSIS package into Stored
Packages/MSDB/Maintenance Plans folder under the Integration Services node of
Object Explorer in SQL Server Management Studio (as well as creating a
corresponding SQL Server Agent job), you will not find it in the Maintenance
Plans subfolder of the Management folder under the Database Engine node (within
the same Object Explorer window). This potentially confusing behavior appears
to be intentional. Each time a new SSIS package gets stored in an msdb database,
its information is entered into a separate row in the dbo.sysdtspackages90
table. Its columns contain such parameters as the package name, its unique GUID
identifier, its content (in image data type format), creation date, identifier
of the Integration Services node subfolder where its graphical representation
is displayed, as well as package type. This last piece of data indicates the
client program that created the package and includes such options as SQL Server
Import and Export Wizard (1), DTS Designer in SQL Server 2000 (2), SQL Server
Replication (3), SSIS Designer (5), and Maintenance Plan Designer (6). In order
for packages to be displayed in the Maintenance Plans subfolder of Management
folder under the Database Engine node, values of folderid and packagetype
parameters must be equal to ’08aa12d5-8f98-4dab-a4fc-980b150a5dc8′ and 6,
respectively (which explains why maintenance plans packages created with SSIS
Designer and imported subsequently into an msdb database, with packagetype
value of 5, do not appear in the Maintenance Plans subfolder of the Management
folder under the Database Engine node ).

This, however, is not the only reason that standard SSIS packages are
separated from their counterparts created within SQL Server Management Studio.
More importantly, there seems to be no reliable method of converting from one
to the other. Subplans (along with their scheduling, logging, and reporting
properties) are not represented in a manner that can be properly configured
once imported into the SSIS Designer interface. Similarly, attempts to include
any additional SSIS components into existing maintenance plans (after exporting
them into a DSTX file, modifying with SSIS Designer, and re-importing into
Maintenance Plan designer) do not yield consistent results. This situation
appears to be a direct result of changes (such as, individually scheduled subplans)
introduced with Service Pack 2 (although, as the Knowledge Base article 922651
indicates, even prior to its release, there were some compatibility issues
between the two types of maintenance plans). In general (as hinted in the
Readme for Microsoft SQL Server 2005 Service Pack 2
), it is beneficial to
maintain consistency when choosing tools for creating and modifying maintenance
plans .

This concludes our short subseries focusing on the Maintenance Plan tasks.
We will resume discussion of the remaining core features of Integration
Services in our next article.

»


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