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 24, 2006

SQL Server 2005 Integration Services - Packages Deployment - Part 23

By Marcin Policht

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 deployment procedures.

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 DTUtil /? 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, /SQL, /FILE, or /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 COPY, MOVE, DELETE, or 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 Online.

» 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