Common SQL Server Integration Services (SSIS) package deployment scenarios

Prepare SQL Server Integration Services (SSIS) projects for deployment and testing with assistance from this excerpt from "Inside SQL Server 2005 Tools."

Common package deployment scenarios

After you complete the development or modification of packages, you need to deploy them to a test environment for further testing or to a production environment. This section includes information about common tasks that you do to prepare Integration Services projects for deployment, create the deployment bundle, and install packages on the target computer.

  • How to add packages and other files to an Integration Services project
  • How to configure the deployment utility and build a project
  • How to prepare to install packages on the target computer
  • How to install packages, package dependencies, and other files

How to add packages and other files to an Integration Services project

The deployment bundle includes all packages in an Integration Services project. If you want to deploy only a subset of the packages, you must create a new Integration Services project and add those packages to the project. If you want to include packages from other projects, you must add the packages to the project from which you build the deployment utility. Integration Services automatically identifies and includes package dependencies in the deployment bundle, so you do not need to explicitly add those items to the project. Package dependencies include any configuration files that the package uses. On the other hand, the log files that the package uses are not automatically included. To decide whether you need to include the log files, you need to know whether the File connection manager creates a new file or uses an existing file. The easiest way to do this is to locate the pertinent File connection managers in the Connection Managers area of SSIS Designer, double-click it, and take a look at the Usage type option. If it is set to Create file, you do not need to include the log files, but if it is set to Existing file, and the file does not already exist on the target server, you do. Tasks such as the Execute SQL, XML, and WMI Data Reader tasks that execute language statements can be configured to use a direct input, a File connection, or a variable to provide the statement. If the task uses statements stored in a file, then you must add that file to the project. Again, if the file already exists on the target server, you need not do this.

If you add packages to the project, they are automatically added to the SSIS Packages folder; other items are added to the Miscellaneous folder.

How to Configure the Deployment Utility and Build a Project

Before the build process that Business Intelligence Development Studio provides for Integration Services projects will generate the deployment utility and create the deployment bundle that you use to install packages, you must update project properties. In Solution Explorer right-click the project and click Properties, or select the project and then click Properties on the Project menu. Figure 16-44 shows an example of the resulting dialog box.

Figure 16-44The Property Pages dialog box for the Integrations Services project, MyProject.

You need to configure only three properties for the deployment utility: AllowConfigurationChanges, CreateDeploymentUtility, and De¬ploy¬mentOutputPath. To show these properties, click Deployment Utility in the Property Pages dialog box. By default, the AllowConfigurationChanges property is set to True. This means that you can update the values of configurations when you install the packages on the target computer. Typically, you want to allow the update of configurations the first time you deploy a set of packages. Depending on the updates to packages, you may want to disallow the update of configurations on package redeployment to ensure that package properties are not inadvertently changed and the redeployed package works the same way as the package it replaces.

To build the deployment utility when you run the build process, set the CreateDeploymentUtility property to True. The default value, binDeployment, of DeploymentOutputPath, specifies the folder relative to the Integration Services project for which you build the deployment utility to store the files in the deployment bundle.

Next, you right-click the Integration Services project and click Build. After the build process completes, the Deployment folder contains a deployment manifest, .SSISDeploymentManifest, the packages, and any package dependencies.

How to prepare to install packages on the target computer

Depending on the configuration of the packages you want to deploy, you may have work to do on the target computer before packages can be run successfully in the new environment.

The following are some common tasks:

  • Create environment variables. The environment variables that configurations use must exist on the target server.
  • Create Registry keys. The Registry keys that configurations use must exist and they must include a value named Value. The value on the target computer can be a string or a DWORD.
  • Create SQL Server tables for configurations. If you want to use a different SQL Server database than the one used during package development, you must re-create the configuration table in that database.
  • Create SQL Server tables for log entries. If you want to use a different SQL Server database than the one used during package development, you must re-create the configuration table in that database.
  • Create a share on the target computer to which you can copy the deployment bundle.

How to install packages, package dependencies, and other files

After you complete the build process to create the Deployment folder, you are ready to install the packages on the target computer.

To install packages on a different computer, follow these steps:

  1. Copy the Deployment folder to the target computer.
  2. Locate the deployment bundle and double-click the manifest file, .SSISDeploymentManifest. The Package Installation Wizard starts.
  3. On the welcome page click Next.
  4. On the Deploy SSIS Packages page, choose whether to install packages to the SQL Server Database Engine or the file system. Also, decide whether to validate packages after installation.
  5. If installing on SQL Server, specify the server name and authentication type on the Specify Target SQL Server page.
  6. On the Select Installation Folder page, specify the folder in the file system for the package (in a file system installation) and for package dependencies.
  7. If any of the packages uses configurations, the Configure Packages page opens. On this page, you can edit configurations by updating values in the Value list.
  8. Depending on whether you decided to validate packages, the packages are validated.

Use the following table of contents to navigate to chapter excerpts, or click here to view Inside Integration Service Tools in its entirety.




Inside SQL Server Integration Services Tools

  Home: Introduction
 Part 1: Integration Services overview
 Part 2: Integration Services tools overview 
 Part 3: Using Integration Services tools in business scenarios 
 Part 4: Common package development scenarios 
 Part 5:Common package deployment scenarios
 Part 6: Common package management scenarios 

About the book   
Microsoft SQL Server 2005's high-powered management tools can dramatically improve DBA productivity and effectiveness. Now there's a comprehensive guide to SQL Server 2005's toolset, straight from the Microsoft team that created it. This book covers the entire toolset in unprecedented depth, guides database professionals in choosing the right tools, and shows them how to use various tools collectively to solve real-world problems. Purchase Inside SQL Server 2005 Tools from Addison-Wesley.
About the author   
Lead author Michael Raheem is a senior product manager in the SQL Server Marketing team at Microsoft. Michael currently leads the SQL Server enterprise marketing efforts, including high availability, scalability, performance, and SQL Server Always On Technologies. Prior to joining the marketing team, he led the design and implementation of several SQL Server 2005 tools such as Management Studio, Upgrade Advisor, Database Mail and Surface Area Configuration. Michael has spoken at several conferences, including TechEd, TechReady, PASS and SQL Connections. Additionally, he has contributed to the Answers from Microsoft column in SQL Server Magazine and has over 13 years of experience in designing and developing solutions with Microsoft SQL Server.


Dig Deeper on Database software management