Readme_Lookup Transformation Sample

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

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.

The Lookup Transformation sample is a sample Integration Services package that implements the Lookup transformation in full cache mode using the Cache connection manager. The transformation performs lookups on a reference dataset that is stored in a text file.

The two data flows in the package demonstrate the following tasks:

• The first data flow loads a reference dataset from a text file, and uses a Cache Transform transformation to populate a Cache connection manager with the data.
• The second data flow loads a data source from a text file. The data flow then uses the Lookup transformation to perform lookups by joining data in the input columns with columns in the reference dataset. The Lookup transformation uses the Cache connection manager to access the reference dataset and cache the reference dataset before the transformation runs.
• The second data flow also redirects data from the Lookup transformation to different Flat File destinations. These different destinations correspond to rows with matching entries in the reference dataset, rows without matching entries, and rows with truncation errors. To count the rows directed to each Flat File destination and store this count information in user-defined variables, the second data flow uses a Row Count transformation.

If you run the sample on a non-English version of Windows, you might have to substitute the localized name of the Program Files folder to open or run the sample.

Requirements

To run this sample package, the following components are required:

• The sample package and data files that the package uses must be installed on the local hard disk drive.
• If you run the sample package only from the command line, you must install Integration Services.
• If you open the package in SSIS Designer and run the sample package, you must 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 Package

If you installed the samples to the default installation location, the Lookup sample package is located in the following folder:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\

To run this sample package, the files listed in the following table must be stored on the computer that is running the package.

Running the Sample

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

If you are using a non-English version of Windows, you might have to update the ConnectionString property of any file connection managers that the package uses. Otherwise, the sample package might not run successfully.

Before running the package, verify that the path that the connection manager uses is valid on your computer. If you have to, modify the path that the connection manager uses so that the path uses the localized name of the Program Files folder.

For this sample, you might have to update "Program Files" in the ConnectionString property for the Create_Execute_Process_Dest and customers connection managers.

To run the package by using dtexec

1. Open a Command Prompt window.

2. Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn.

   This directory contains the dtexec utility.

3. Type the following command:

   dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\LookupSample\LookupSample.dtsx"
   

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

To run the package in Business Intelligence Development Studio

1. Open Business Intelligence Development Studio.

2. Create a new project and a new Integration Services solution.

   The new solution is added to Solution Explorer, and the new project is added to the solution. For more information, see How to: Create a New Integration Services Project.

3. In Solution Explorer, right-click the SSIS Packages folder, and then click Add Existing Package.

4. Add the LookupSample.dtsx package by doing the following steps:

   1. In the Package location list, select File System.
   2. Click the browse button (...), select the LookupSample.dtsx package, and then click OK.

5. In Solution Explorer, in the SSIS Packages folder, right-click LookupSample.dtsx, and then click Execute Package.

Components in the Sample

The following table lists the Integration Services tasks and connection managers that the sample uses.

Sample Results

The execution results of this sample package are saved to three flat files, which are located in the following folder:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\DataFiles