Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 14, 2006

SQL Server 2005 - SQL Server Integration Services - Package Management - Part 22

By Marcin Policht

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:

  • Do not save sensitive - removes sensitive information during the save. When the saved copy is opened, removed items (passwords, etc.) need to be added again in order to restore package to its original state. This also means that its execution (without re-adding missing information) will likely fail.
  • Encrypt all with password - encrypts the entire package with a password specified during the save using the Triple DES cipher algorithm with 192-bit key. Knowledge of the password is required in order to execute the package or to open it for editing in Business Intelligence Development Studio.
  • Encrypt all with user key - similar to the previously described level, encrypts the entire package but instead of using a password, makes the process transparent by applying a user-specific key stored securely in the user's personal profile. This means that executing the package or reopening it in Business Intelligence Development Studio must be performed not only by the same user but also requires access to the personal profile. As a result, this level is not suitable for shared development efforts or transferring packages between systems (protecting them with passwords is a more appropriate option).
  • Encrypt sensitive with password - uses the same encryption mechanism as the "Encrypt all with password" level, but applies it only to sensitive information. This means that in order to successfully execute a package encrypted in this manner you will need to supply the password (otherwise, the execution will fail). On the other hand, you will be able to open such packages in the Business Intelligence Development Studio even without password knowledge, but during this process, the sensitive information will be automatically removed.
  • Encrypt sensitive with user key - uses the same encryption mechanism as the "Encrypt all with user key" level, but applies it only to sensitive information. Execution of such packages without access to the encryption key (i.e. either by another user or by the original user with profile missing) will result in its failure. It is possible to open the package for edits in the same circumstances, but with sensitive information automatically removed. Note that this is the default protection level applied to packages created with Business Intelligence Development Studio (to verify this, right-click on the empty area of the Control Flow tab in the SSIS Designer, select Properties item from the context sensitive menu, and check ProtectionLevel entry in the Properties window).
  • Rely on server storage and roles for access control - does not perform encryption, but instead leaves protection of the package content to the permissions mechanism built into SQL Server (this option is applicable only when storing the package in msdb database). For this purpose you can use fixed database-level roles, such as db_dtsadmin (with administrative rights to SSIS packages stored on SQL Server), db_dtsltduser (with ability to execute individual SSIS packages to which permissions have been granted), and db_dtsoperator(allowed to execute, backup, and restore all SSIS packages). Custom roles (Reader and Writer) can be granted using the Package Roles dialog box (displayed by selecting the Package Roles... item in the context sensitive menu of packages stored in SQL Server from Object Explorer in SQL Server Management Studio).

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.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM