Readme_Creating a Simple ETL Package Tutorial: Lesson Packages

This sample works only with SQL Server 2012. The sample works with the SQL Server 2012 version of the AdventureWorks DW database (AdventureWorksDW2012). To install this database, go to Adventure Works for SQL Server 2012.

Download the sample from the DOWNLOADS tab.

The Lesson 1, Lesson 2, Lesson 3, Lesson 4, Lesson 5, and Lesson 6 packages are completed packages from the SSIS Tutorial: Creating a Simple ETL Package. Each lesson package builds on the version of the package created in the earlier tutorial lessons. These topics provide detailed information about the functionality of each package. The topics are located in the Integration Services Tutorials section of MSDN library.

You can open and run these packages outside the tutorial. You need to do the following to ensure that the packages run successfully.
  • Run the Lesson 1 package first to create the NewFactCurrencyRate table.
  • For the Lesson 1 and Lesson 2 packages, update the configuration of the Flat File connection manager to point to the location of the sample data on your machine.
  • For the Lesson 5 package, create the XML configuration file. For instructions on how to create the file, see Lesson 5: Adding Package Configurations for the Package Deployment Model.
  • The Lesson 5 package uses the CurrencyBAD.txt file. _If you want to use the file provided instead of creating the file as instructed in the Lesson 3 tutorial documentation, use the CurrencyBAD.txt file that is included with the lesson packages.

The following table provides a brief summary of each lesson 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.
Lesson 6 In this lesson, you will expand the package you created in Lesson 5 to deploy the project to the Integration Services server using the Project Deployment model. You will replace the configuration value with a parameter to specify the sample data location.

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.


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 SQL Server Data Tools.

Running the Sample

The packages can be run from the command line by using the dtexec utility, or the packages can be run in SQL Server Data Tools.

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.

To run the package by using dtexec
  1. Open a Command Prompt window.
  2. Change the directory to C:\Program Files\Microsoft SQL Server\110\DTS\Binn, the location of dtexec.
  3. Type the following command: dtexec /f "<full path of package file>".
  4. Press Enter.

For more information about using the dtexec utility to run a package, see dtexec Utility.

Running the Lesson Packages in SQL Server Data Tools
If you do not already have an Integration Services project to which you can add the lesson packages, you must first create a project.

To create a new Integration Services project
  1. Open SQL Server Data Tools.
  2. On the File menu, point to New and then click Project.
  3. In the New Project dialog box, expand the Business Intelligence node in the Installed Templates pane, and then select the Integration Services template.
  4. In the center pane, select Integration Services Project.
  5. Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
  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 add a package to the project and run the package in SQL Server Data Tools
  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 lesson packages.
  3. Click the package you want to add, and then click Open.
  4. Click OK.
  5. Repeat steps 1-4 to add additional packages to the project.
  6. Right-click a package to run in the SSIS Packages folder in Solution Explorer, and then click Execute Package.

Last edited Jan 2, 2013 at 11:27 PM by sabottaca, version 13


No comments yet.