Managing Packages

SQL Server Management Studio allows you to manage packages that are currently executing and those that are stored in SQL Server. The following sections describe how to interact with both types of packages.

Running Packages

After you've established a connection in Management Studio, there will be an Integration Services node in Object Explorer with two subnodes called Running Packages and Stored Packages. The Running Packages folder displays the packages that are currently executing on the node's server and updates regularly as packages start and finish executing. You can also force a package to stop by right-clicking on the package displayed in the Running Packages tree and selecting the Stop menu option. To see this feature, run the sample package WaitUntilTerminated in the sample solution S17-ManagementStudio.

To simulate a long-running package, the WaitUntilTerminated package has two Script Tasks that show message boxes. The second Script Task is constrained with a completion precedence constraint on the first. If you run the package, it shows a message box. You can then refresh the Running Packages node in Management Studio to show the WaitUntilTerminated package. To stop it, right-click on the WaitUntilTerminated package in the Management Studio Running Packages node and select the Stop menu option. Then, wait a second or two and then click the OK button in the message box to dismiss it, and the package will terminate.

Note

You need to dismiss the message box because the Stop option does not orphan executing tasks or force packages to stop executing. Rather, it sets the Cancel event in the package object that tells it to cease scheduling new tasks for execution. Therefore, when using the Stop feature, some packages do not immediately stop, especially if tasks aren't checking the Cancel event or calling the FireQueryCancel event regularly.

All packages that are prematurely stopped in this way log that fact in the system log. You can see the message in the Event Viewer in Figure 17.11. This ensures that you have a record of any packages that are canceled.

Figure 17.11. Packages log a message whenever they are canceled

Stored Packages

The Stored Packages folder has two subfolders. The File System folder lists the packages that have been stored on the local server. The MSDB folder lists packages stored to the SQL Server instance where the IS Service is configured to enumerate packages.

Note

There is nothing special about the File System folder. In fact, it simply reflects the folder structure of the underlying file system folder. By default, that folder is found at %Program Files%\Microsoft SQL Server\90\DTS\Packages. If you add or delete packages or folders directly to or from the file system folder, they show up in the Management Studio File System node. Packages must be named with a .dtsx extension to be included in the File System node.

If you right-click on a package under the Stored Packages node, a context menu appears with one or more of the following menu options:

• New Folder Creates a new subfolder

• Import Package Imports the package from the file system, SQL Server, or SSIS Server storage

• Export Package Exports the package from the location it is stored to the file system, SQL Server, or SSIS Server storage

• Package Roles Launches the Package Roles dialog box for you to select the roles for the package; this feature is not supported for packages stored in the file system

• Run Package Executes the package using the DTExecUI utility

• Delete Deletes the package

• Rename Renames the package

Although these two folders have no subfolders by default, you are free to create, rename, and delete subfolders as you want using the New Folder menu option. When you create a new folder beneath the File System folder, a new directory is created in the file system as well. For folders that are created under the MSDB folder, a new entry is added to the sysdtspackagesfolder90 table that tracks the folder structure. Subfolders make it possible to better organize packages. If you have multiple packages in a solution, you can group them together into one subfolder and give the folder the same name as the solution. This makes it easier to link the production storage location with the solution. In the same way that having all your files in the same file system folder would be a nightmare, it's also easier to manage packages grouped hierarchically in this way.

SQL or File System?

Are there any pros/cons for deploying packages into SQL Server versus deploying as a file? The obligatory answer is "It depends." This is not a new question. The options to save packages to SQL Server and to files were available in DTS as well as the option to save to Meta Data Services, which is no longer available in SQL Server 2005. But, it is a good question. It means people are trying to understand the trade-offs. So here are some of them:

Advantages of Saving to Files

• Can use source control to manage

• Ultra secure when using the Encrypt with User Key encryption option

• Not subject to network downtime problems (saved locally)

• May escrow deployment bundles, including miscellaneous files

• Less steps to load into the designer

• Easier direct access for viewing

• May store packages hierarchically in file system

• Projects in Visual Studio are disk based and require the package to be in the file system

• Generally a better experience during development

Advantages of Saving to SQL Server

• Generally easier access by multiple individuals

• Benefits of database security, DTS roles, and Agent interaction

• Packages get backed up with normal DB backup processes

• Able to filter packages via queries

• May store packages hierarchically via new package folders

• Generally a better in-production experience

Managing DTS 2000 Packages

You can also access DTS packages in Management Studio. Whether stored in SQL Server 2005 or SQL Server 2000, the packages are stored in the Legacy tree, as shown in Figure 17.12.

Figure 17.12. DTS packages are stored in the legacy node

If you right-click a package, the context menu has the following options:

• Open Launches the DTS 2000 designer

• Migrate a Package Launches the Migration Wizard to migrate the DTS package to an SSIS package

• Export Exports the package from the msdb database to another SQL Server, the SSIS Server, or the file system

• Delete Deletes the package

• Rename Renames the package

Caution

To launch the DTS Package Editor, you must have an existing SQL Server 2000 instance remaining on the machine, or you must download and install the Microsoft SQL Server 2000 DTS Designer Components. To find these components, search MSDN for "DTS Designer Components." See Chapter 2, "Setting Up Integration Services," for more information about setting up the DTS Package Editor.

Server Caching

Besides package execution monitoring and storage management, the server also acts as a systemwide cache for collections of information objects that describe installed Integration Services components. Because there are so many different pluggable components, it's quite expensive to rediscover all the components every time you run a package. In fact, it's prohibitively time consuming. The server shortcuts that by caching information about each of the installed components. After being cached, clients get information about installed components directly from the server. Following is a list of the component collections the server populates and caches:

• ForeachEnumerators

• Tasks

• LogProviders

• Connections

• DBProviders

• Packages

• PipelineComponents

As a package writer, you might never see these collections because the designer and other tools hide them. However, you will see the benefits of having these collections cached. To understand how caching these collections affects the performance of the designer, stop the Integration Services service and load up an Integration Services project. Open a package. There will be a noticeable delay between when you open the package until when the designer is actually ready to edit it. This is because the designer is busy trying to enumerate all the components.

Note

If the Integration Services Server is not running when you attempt to open a package in the designer, the server automatically starts. From that time forward, the service is available and will have cached the component collections.