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

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 FilesMicrosoft SQL
Server90DTSPackages 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 FilesMicrosoft
SQL Server90DTSPackages 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

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