SQL Server 2005 - SQL Server Integration Services - Package Management - Part 22
April 14, 2006
Throughout our series of articles dedicated to SQL Server 2005 Integration Services, we have been working mainly with packages using the Business Intelligence Development Studio interface (although more recently, we also discussed SSIS-related functionality available in SQL Server Management Studio). Even though, as part of our exercises, we have worked with a number of packages, we have not yet explored the various options related to their storage. We will cover them in this article - including implementation details and security implications of each.
In general, there are two basic ways an SSIS package can be saved. The first one, which you are likely to use more frequently (since it is inherent to Business Intelligence Development Studio), creates an XML-formatted file (with extension .dtsx) in an arbitrarily chosen folder, also hosting other solutions and project files (such as XML-formatted .dwproj, .dtproj, or .ds). The folder is typically designated at project initiation (following the selection of the New -> Project... option from the File menu in the Business Intelligence Development Studio) in the New Project dialog box, where you are prompted to provide its full path in the Location text box (as well as decide whether you want to have a separate directory for the solution this project is part of). Subsequently, this location can be easily determined by checking the Full Path entry in the package Properties dialog box.
The second destination store of SSIS packages - SQL Server 2005 msdb database - was most commonly used with the SSIS predecessor - Data Transformation Services (DTS) in SQL Server 7.0 and 2000. Similar to previous versions, packages and their associated meta-data reside in a number of inter-related tables, with sysdtspackages90 storing their actual content (sysdtspackages is intended for their DTS counterparts), and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog serving supporting roles. There is also the third destination, referred to as SSIS Package Store, which corresponds to the Program Files\Microsoft SQL Server\90\DTS\Packages folder (assuming that SQL Server has been installed in the default target directory). Packages placed there are managed by SSIS service, however, in general, they exhibit functionality equivalent to the ones stored in file system. Note that it is no longer possible to save packages to Metadata Services, aswas the case in SQL Server 2000.
As we already mentioned, most commonly a package starts its existence as a .dtsx file in the Business Intelligence Development Studio and remains in this format during its design stage. At any given point, though, you have the ability to save it to an arbitrary file system location choosing the "Save As..." item in the File menu, or duplicate it by selecting "Save Copy As..." ("Save Selected Items" menu item can be applied to any number of packages within the same project after you multi-select them in the Solution Explorer window). While the first one results simply in the creation of another file (which location and name you need to provide), the second one gives you more flexibility reflected by a variety of options in the Save Copy of Package dialog box. In its drop down list, labeled "Package location", you can choose among SQL Server, File System, and SSIS Package Store destinations. Depending on your choice, you might need to specify a target Server name (this is necessary if SQL Server or SSIS Package Store are selected) and authentication method (applicable only to SQL Server). In addition, it is also necessary to designate Package path. When saving to File System, you are required to provide a local or remote folder path along with the name of the .dtsx file. With SQL Server or SSIS Package Store options, you are presented with another dialog box, where you can decide which folder in the SSIS Packages hierarchy (and which package name) you want to use. This hierarchy consists of File System (corresponding to the Program Files\Microsoft SQL Server\90\DTS\Packages directory) and msdb database nodes (both of them allow you to create custom subfolder hierarchy to organize packages according to your arbitrary requirements). Finally, you also have the ability to set the package protection level.
Protection Level determines the way package content is secured. This might be applied to the entire package or limited only to such sensitive information as passwords included in the data connection string, or other properties of tasks, connection managers, or variables that have their XML-representation in the package file marked by their creators (either Microsoft or a third-party) with "Sensitive" attribute. There are six different protection levels:
There is also an alternative method of storing packages with an appropriate level of protection. To review it, launch SQL Server Management Studio (or if you have it running, select the Connect Object Explorer... item from the File menu). This will display the Connect to Server dialog box, in which you need to point to Integration Services as the Server type entry and specify the server name (and provide authentication information, if necessary). Once the connection is established, you will be presented with the Integration Services top level node in the Object Explorer window, with Running Packages and Stored Packages subnodes underneath. Expanding Stored Packages will provide the view of its two default subfolders - File System (which actually designates SSIS Package Store) and msdb. Right click on the intended target and select the Import Package... option from the context sensitive menu. This will display the Import Package dialog box, with an interface identical to the just described Save Copy of Package (and equivalent functionality).
Packages stored in msdb database (sysdtspackages90 and sysdtspackages tables) are automatically included in its backups. While backing up the file system is just as straightforward, keeping your packages within SQL Server might be beneficial if the management of backups and restores is the responsibility of the same team that creates SSIS packages and handles their execution. In general, during the development stage, it is more convenient to store packages in files, since they can be directly loaded into the Business Intelligence Development Studio (msdb-resident packages need to be first exported from Object Explorer of SQL Server Management Studio) or viewed (and edited) in Windows Explorer using any text or XML editor. Once they are ready for deployment in a production environment, you should consider importing them into SQL Server, which provides you with a consistent management interface and an additional layer of built-in security.