In the previous
installment of this series, we started exploring different ways to store SQL
Server 2005 Integration Services packages. As presented, this can be
accomplished by saving them as .dtsx files (either in an arbitrary local or
remote location or within the designated area known as SSIS Package Store,
corresponding in case of the default installation to C:\Program Files\Microsoft
SQL Server\90\DTS\Packages folder) or as entries in the sysdtspackages90 table
of msdb database. We looked at two methods that provide this ability (the first
one accessible via "Save Copy of Package" dialog box in Business
Intelligence Development Studio and the second one configurable via
Import/Export Package dialog boxes in SQL Server Management Studio), as well as
at settings related to security of packages residing in both types of
locations. In this article, we will continue our discussion by exploring other
mechanisms for managing their storage while focusing our attention on
The manual methods of saving packages we have described earlier are intended
primarily for simpler, single-package projects. However, if you tend to deal
more frequently with projects composed of several packages, which in addition
contain custom components or employ configurations (as you might recall from one of
our past articles, configurations are commonly used when moving packages
from development to production environment or when running them on multiple
servers, for which individual properties or variables need to be appropriately
adjusted), you might want to consider another approach, which involves use of
the SSIS Deployment Utility (otherwise, you have to remember to copy all
associated files manually). This handy feature is designed to generate easily
transferable and executable collections of files included within the project
that are needed for deployment at a target destination.
The SSIS Deployment Utility is accessible from the Solution Explorer window
of Business Intelligence Development Studio. To take advantage of it, right
click on the top node representing the project and select the Properties item
from its context sensitive menu (or use the Properties item in the Project top
level main menu). In the resulting dialog box, switch to the Deployment Utility
entry in the Configuration Properties list appearing on the left hand side.
This will reveal three properties in the grid displayed on the right:
AllowConfigurationChanges - Boolean value (i.e. True, which is
the default, or False) that determines whether it will be possible to choose
among package configurations during its deployment and assign values of
properties or variables they reference.
CreateDeploymentUtility - Boolean value (True or False, which is
the default) that indicates whether initiating the project build will result in
the creation of its Deployment Utility.
DeploymentOutputPath - path that points to target directory where
the Deployment Utility will be created. The path is relative to the location
where project files reside (and set, by default, to bin\Deployment).
Once you assign the value of CreateDeploymentUtility property to True and
start the Build process (using Build item in the top level main menu), you will
populate the output folder (designated by DeploymentOutputPath property) with
the .dtsx file (or files, depending on the number of packages in your project),
XML-formatted Deployment Manifest file (whose name is constructed by
concatenating the project name and .SSISDeploymentManifest suffix) and,
potentially, a number of other, project related files (such as custom
components or package configurations).
The next step in the deployment process involves copying the entire content
of the Deployment Output folder to a target server and double-clicking on the
Deployment Manifest file at its destination. This action will automatically
trigger the launch of the Package Installation Wizard. After its first, purely
informational page, you will be prompted to choose between File system and SQL
Server deployments (for a review of both options, refer to our previous
article). You also can decide whether you want to validate packages following
their installation. Depending on your choice regarding storage type, you will
be asked, on the next page of the wizard, to either specify a target folder or
target server along with appropriate authentication information (in case of the
latter, you might also be required to provide a folder path where other package
specific files - such as configurations - will be saved). Note, that you still
have the ability to save packages in the SSIS Package Store by selecting File
System and keeping the C:\Program Files\Microsoft SQL Server\90\DTS\Packages
location (which happens to be the default value) as the destination folder. If
you included package configuration files with your project (and set AllowConfigurationChanges
property to True prior to generating Deployment Utility), you will also have a
chance to select the one you intend to use and set values of properties or
variables referenced by it. Note, however, that there is no option to set the
protection level (which forces you to rely on either NTFS permissions or SQL
Server database roles for securing access to your packages and sensitive
information they might contain).
The Package Installation Wizard, although straightforward and easy to use,
it is not well suited for deploying larger numbers of packages. If you need to
handle such cases, you might want to employ the DTUtil.exe command line utility
instead, which in addition to its versatility (including support for
encryption), can be conveniently incorporated into batch files. Its syntax can
be easily reviewed by checking the output of
/? typed at the Command Prompt and has the format:
DTUtil /option [value] [/option [value]] ...
which means that the utility's name is followed by a number
of pairs consisting of the desired option name (case-insensitive), with leading
forward slash (or a dash) and one or more trailing values. For example,
/DTS options designate
the location of a package (SSIS Package Store, File System, and msdb database,
respectively) and are paired with a value that identifies the exact location of
each (relative or absolute path). They typically accompany
EXISTS, which take care of copying,
moving, deleting, or verifying existence for a specific package (note that each
of these options is followed by either SQL, FILE, or DTS entry - depending on the package store type - a semicolon, and a value detailing exact package location).
/ENCRYPT, combined with an integer
representing a specific protection level allows securing the package content
through encryption. The integer values are as follows:
0 corresponding to "Do not save sensitive"
1 to "Encrypt sensitive with user key"
2 to "Encrypt sensitive with password"
3 to "Encrypt all with password"
4 to "Encrypt all with user key"
5 to "Rely on server storage and roles for access control"
For example, the command line below copies a specific
package from its File System location to SQL Server:
DTUtil /FILE "C:\SSIS_Packages\Heck_of_a_job.dtsx" /COPY SQL;"Brownie"
The reverse process of moving a package residing in an msdb database to a file,
can be done with:
DTUtil /SQL "Rummy" /MOVE FILE;"c:\SSIS_Packages\Mission_accomplished.dtsx"
To encrypt the entire package with a password and store the outcome, you
would apply syntax similar to the following:
DTUtil /FILE "c:\SSIS_Packages\Rummy.dtsx" /ENCRYPT FILE;"c:\SSIS_Packages\Dick.dtsx";3;"duck"
Note that DTUtil does not provide the ability to automatically incorporate
additional files associated with a package that are part of the same project
during copies or moves (which, as described above, can be accomplished with the
Deployment Utility). For more details regarding syntactical and functional characteristics
of the DTUtil.exe, refer to its description in the SQL Server 2005 Books
See All Articles by Columnist Marcin Policht