SSIS 03: Deploy Packages with SSIS

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.

 

Lessons in This Tutorial

Lesson 1: Preparing to Create the Deployment Bundle
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.

Lesson 2: Create the Deployment Bundle in SSIS
In this lesson, you will build a deployment utility and verify that the deployment bundle includes the necessary files.

Lesson 3: Install SSIS Packages
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 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 tutorial: SSIS How to Create an ETL Package.

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

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

  • SQL Server
  • Sample data, completed packages, configurations, and a Readme. These files are installed together if you download the Adventure Works 2014 Sample Databases.
  • SQL Server Management Studio.
  • SQL Server Integration Services.
  • 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 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.

Estimated time to complete this tutorial: 2 hours

 

SSIS 02: How to Create an ETL Package

In this tutorial, you learn how to use SSIS Designer to create a simple Microsoft SQL Server Integration Services package. The package that you create takes data from a flat file, reformats the data, and then inserts the reformatted data into a fact table. In following lessons, the package is expanded to demonstrate looping, package configurations, logging, and error flow.

When you install the sample data that the tutorial uses, you also install the completed versions of the packages that you create in each lesson of the tutorial. By using the completed packages, you can skip ahead and begin the tutorial at a later lesson if you like. If this tutorial is your first time working with packages or the new development environment, we recommend that you begin with Lesson1.

Lessons in This Tutorial

Lesson 1: Create a Project and Basic Package with SSIS
In this lesson, you create a simple ETL package that extracts data from a single flat file, transforms the data using lookup transformations and finally loads the result into a fact table destination.

Lesson 2: Adding Looping with SSIS
In this lesson, you expand the package you created in Lesson 1 to take advantage of new looping features to extract multiple flat files into a single data flow process.

Lesson 3: Add Logging with SSIS
In this lesson, you expand the package you created in Lesson 2 to take advantage of new logging features.

Lesson 4: Add Error Flow Redirection with SSIS
In this lesson, you expand the package you created in lesson 3 to take advantage of new error output configurations.

Lesson 5: Add SSIS Package Configurations for the Package Deployment Model
In this lesson, you expand the package you created in Lesson 4 to take advantage of new package configuration options.

Lesson 6: Using Parameters with the Project Deployment Model in SSIS
In this lesson, you expand the package you created in Lesson 5 to take advantage of using new parameters with the project deployment model.


What You 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 SSIS Designer to create a simple ETL package that includes looping, configurations, error flow logic, and logging.

Requirements

This tutorial is intended for users familiar with fundamental database operations, but who have limited exposure to the new features available in SQL Server Integration Services.

To use this tutorial, your system must have the following components installed:

  • SQL Server with the AdventureWorksDW2012 database. To download the AdventureWorksDW2012 database, download AdventureWorksDW2012.bak from AdventureWorks sample databases and restore the backup.
  • Sample data. The sample data is included with the SSIS lesson packages. To download the sample data and the lesson packages as a Zip file, click here.

SSIS 01: SQL Server Integration Services Tutorials

This section contains tutorials Integration Services.

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

Try SQL Server and SQL Server Integration Services

info_tip Resources