Deploying Packages Tutorial
New: 14 April 2006
Microsoft SQL Server 2005 Integration Services (SSIS) provides tools that make it easy to deploy packages to another computer. The deployment tools also manage any dependencies, such as configurations and files that the package needs. In this tutorial, you will learn how to use these tools to install packages and their dependencies on a target computer.
First, you will perform tasks to prepare for deployment. You will create a new Integration Services project in Business Intelligence Development Studio and add existing packages and data files to the project. You will not create any new packages from scratch; instead, you will work only with completed packages that were created just for this tutorial. You will not modify the functionality of the packages in this tutorial; however, after you have added the packages to the project, you might find it useful to open the packages in SSIS Designer and review the contents of each package. By examining the packages, you will learn about package dependencies such as log files and about other interesting features of the packages.
In preparation for deployment, you will also update the packages to use configurations. Configurations make the properties of packages and package objects updatable at run time. In this tutorial, you will use configurations to update the connection strings of log and text files and the locations of the XML and XSD files that the package uses. For more information, see Package Configurations and Creating Package Configurations.
After you have verified that the packages run successfully in Business Intelligence Development Studio, you will create the deployment bundle to use to install the packages. The deployment bundle will consist of the package files and other items that you added to the Integration Services project, the package dependencies that Integration Services automatically includes, and the deployment utility that you built. For more information, see Creating a Deployment Utility.
You will then copy the deployment bundle to the target computer and run the Package Installation Wizard to install the packages and package dependencies. The packages will be installed in the msdb SQL Server database, and the supporting and ancillary files will be installed in the file system. Because the deployed packages use configurations, you will update the configuration to use new values that enable packages to run successfully in the new environment.
Finally, you will run the packages in SQL Server Management Studio by using the Execute Package Utility.
It is the goal of this tutorial to simulate the complexity of real-life deployment issues that you may encounter. However, if it is not possible for you to deploy the packages to a different computer, you can still do this tutorial by installing the packages in the msdb database on a local instance of SQL Server, and then running the packages from SQL Server Management Studio on the same instance.
What You Will Learn
The best way to become acquainted with the new tools, controls, and features available in Microsoft SQL Server 2005 Integration Services is to use them. This tutorial walks you through the steps to create an Integration Services project and then add the packages and other necessary files to the project. After the project is complete, you will create a deployment bundle, copy the bundle to the destination computer, and then install the packages on the destination computer.
| Lessons | Description |
|---|---|
In this lesson, you will get ready to deploy an ETL solution by creating a new Integration Services project and adding the packages and other required files to the project. |
|
In this lesson, you will build a deployment utility and verify that the deployment bundle includes the necessary files. |
|
In this lesson, you will copy the deployment bundle to the target computer, install the packages, and then run the packages. |
Requirements
This tutorial is intended for users who are already familiar with fundamental file system operations, but who have limited exposure to the new features available in SQL Server 2005 Integration Services. To better understand basic Integration Services concepts that you will put to use in this tutorial, you might find it useful to first complete the following Integration Services tutorials: Creating a Basic Package Using a Wizard and Creating a Simple ETL Package Tutorial.
Source computer. The computer on which you will create the deployment bundle must have the following components installed:
- SQL Server 2005 with the AdventureWorks database. To enhance security, the sample databases are not installed by default. To install the sample databases, see Running Setup to Install AdventureWorks Sample Databases and Samples.
- You must have permission to create and drop tables in AdventureWorks.
- This tutorial also requires sample data, completed packages, configurations, and a Readme. The files for these items are installed together with the samples. If you cannot find the sample data, return to the procedure above and complete installation as described.
- The business intelligence development environment, Business Intelligence Development Studio.
Destination computer. The computer to which you deploy packages must have the following components installed:
- SQL Server 2005 with the AdventureWorks database.
- SQL Server Management Studio.
- SQL Server 2005 Integration Services (SSIS).
- You must have permission to create and drop tables in AdventureWorks and to run packages in SQL Server Management Studio.
- You must have read and write permission on the sysdtspackages90 table in the msdb SQL Server system database.
If you plan to deploy packages to the same computer as the one on which you create the deployment bundle, that computer must meet requirements for both the source and destination computers.
Note
It can be easier to work through a tutorial if you add Next and Previous buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.
Estimated time to complete this tutorial: 2 hours