Share via

Creating a Simple ETL Package Tutorial: Lesson Packages

  • Article

New: 5 December 2005

The Lesson 1, Lesson2, Lesson 3, Lesson 4, and Lesson 5 packages are the completed packages from the Integration Services tutorial, Creating a Simple ETL Package Tutorial. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Creating a Simple ETL Package Tutorial" located in the Integration Services Tutorials section of Books Online. Each lesson's package builds on the version of the package created in the earlier lessons.

You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. Modification of tutorial packages may give you results that differ from those defined in the tutorial lessons.

The following table provides a brief summary of each package.

Lessons Description

Lesson 1

A simple ETL package that extracts data from a single flat file, transforms the data by using lookup transformations, and finally loads the result into a fact table.

Lesson 2

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

In this lesson, you will expand the package you created in Lesson 2 to use a package configuration, variable, and property expression to dynamically update a property.

Lesson 4

In this lesson, you will expand the package you created in Lesson 3 to add logging.

Lesson 5

In this lesson, you will expand the package you created in lesson 4 to use error redirection to capture invalid data in a text file.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

  • The sample packages and data files that it uses must be installed on the local hard disk drive.
  • You must have installed and have administrative permissions on the AdventureWorksDW database.
  • If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
  • If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio.

For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Packages

If the samples were installed to the default installation location, the tutorial sample packages are located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

The Lesson 5 package uses the package configuration file that is created in lesson 3 and used by the Lesson 3 and Lesson 4 packages. Before you run the Lesson 5 package, make sure that the ConfiguredValue element in the SSISTutorial.dtsConfig file contains the location of the Sample Data folder. The default location of this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data.

Also, the Lesson 5 package uses the Currency_BAD.txt file. If you want to use the file provided instead of creating the file, you must copy Currency_BAD.txt from C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages to C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data before you run the package.

If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer and, if necessary, modify the path so that it uses the localized name of the Program Files folder.

For this sample, you may have to update "Program Files" in the ConnectionString property for the Sample Data connection manager.

To run the package by using dtexec

  1. Open a Command Prompt window.

  2. Change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.

  3. Type the following command:

    dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages\<package name>"

  4. Press Enter.

For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

  1. Open Business Intelligence Development Studio.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, select the Integration Services Project template from the Templates pane.

  4. Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.

  5. In the Solution drop-down list, select Create new Solution.

  6. To create a separate folder for the solution file, select Create directory for solution. This is the default option.

  7. Click OK to add the solution to Solution Explorer and add the project to the solution.

Next, you must add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

  1. In Solution Explorer, right-click SSIS Packages and then click Add Existing Package.

  2. In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages.

  3. Click the package to add, and then click Open.

  4. Click OK.

  5. Repeat steps 1-4 to add tutorial packages to the project.

  6. Right-click the package to run in the SSIS Packages folder, and then click Execute Package.