Creating a Simple ETL Package Tutorial

  • Article

Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.

In this tutorial, you will learn how to use SSIS Designer to create a simple Microsoft SQL Server 2005 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 will be 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 will 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 is your first time working with packages or the new development environment, we recommend that you begin with Lesson1.

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

Lessons Description

Lesson 1: Creating the Project and Basic Package

In this lesson, you will 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

In this lesson, you will 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: Adding Package Configurations

In this lesson, you will expand the package you created in Lesson 2 to take advantage of new package configuration options.

Lesson 4: Adding Logging

In this lesson, you will expand the package you created in Lesson 3 to take advantage of new logging features.

Lesson 5: Adding Error Flow Redirection

In this lesson, you will expand the package you created in lesson 4 to take advantage of new error output configurations.

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 2005 Integration Services.

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

  • SQL Server 2005 with the AdventureWorksDW 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.
  • This tutorial also requires sample data. The sample data is installed together with the samples. If you cannot find the sample data, return to the procedure above and complete installation as described.

Note

When reviewing tutorials it is recommended you add Next and Previous buttons to the document viewer toolbar. For more information, see Adding Next and Previous Buttons to Help.

This tutorial presumes you have not reconfigured SSIS Designer to use auto-connect features between control flow elements or between data flow elements. If SSIS Designer uses auto-connect, an element may be connected automatically when added to the design surface. Also, the auto-connect feature for control flow supports the use of Failure and Completion as the default constraint, instead of Success. If SSIS Designer is not using Success as its default constraint, you should reset this configuration while doing the tutorial. You configure the auto-connect features in the Business Intelligence Designers section in the Options dialog box that is available from Options on the Tool menu.

Change History

Release History

5 December 2005
New content:
  • Added information about the completed package you can use to skip ahead to begin the tutorial at any lesson.