Readme_Creating a Simple ETL Package Tutorial: Lesson Packages

11/05/2008 21:36:06

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005. To install the SQL Server 2005 version of the
AdventureWorksDW database, see
Sample Databases for Microsoft SQL Server 2005.

The Lesson 1, Lesson2, Lesson 3, Lesson 4, and Lesson 5 packages are the completed packages from the SQL Server Integration Services tutorial -
SSIS Tutorial: Creating a Simple ETL Package. 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 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 AdventureWorks DW database.

If you intend only to run the sample package from the command line, you must install Integration Services.

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 Books Online.

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\100\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\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data.

Also, the Lesson 5 package uses the CurrencyBAD.txt file. If you want to use the file provided instead of creating the file, you must copy CurrencyBAD.txt from C:\Program Files\Microsoft SQL Server\100\Samples\Integration
Services\Tutorial\Creating a Simple ETL Package\Completed Packages to C:\Program Files\Microsoft SQL Server\100\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

Open a Command Prompt window.

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