Share via

SSIS Tutorial: Deploying Packages

  • Article

Microsoft SQL Server Integration Services 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 SQL Server Data Tools (SSDT) 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 Create Package Configurations.

After you have verified that the packages run successfully in SQL Server Data Tools (SSDT), 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 Create 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 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.

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 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: Tutorial: Creating a Basic Package Using a Wizard and SSIS Tutorial: Creating a Simple ETL Package.

Source computer.   The computer on which you will create the deployment bundle must have the following components installed:

  • SQL Server with the AdventureWorks database. To enhance security, the sample databases are not installed by default. For information about how to install the sample databases, see Getting Started with SQL Server Samples and Sample Databases

  • 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, SQL Server Data Tools (SSDT).

Destination computer.   The computer to which you deploy packages must have the following components installed:

  • SQL Server with the AdventureWorks database.

  • SQL Server Management Studio.

  • SQL Server Integration Services.

  • You must have permission to create and drop tables in AdventureWorksand to run packages in SQL Server Management Studio.

  • You must have read and write permission on the sysssispackages 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

Lessons in This Tutorial

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.