SQL Server 2005 Integration Services - Part 49 - Maintenance Plan Tasks
May 29, 2007
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 Package\MSDB 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.