Readme_Lookup Transformation Sample
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.
| 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. |
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.
| File || Description |
| LookupSample.dtsx|| The sample package. |
| DimTime.csv|| The reference dataset. |
| FactBudget.csv|| The input dataset. |
| MatchOutput.csv|| The text file to which the sample writes rows that have matching entries in the reference dataset. |
| NoMatchOutput.csv|| The text file to which the sample writes rows that do not have matching entries in the reference dataset. |
| ErrorOutput.csv|| The text file to which the sample writes truncation errors. |
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, or if you have installed the samples to a non-default location, 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 correct path to the sample files.
For this sample, you might have to update "Program Files" in the ConnectionString
property for the CreateExecute
Process_Dest and customers connection managers.
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.This directory contains the dtexec utility.
- Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Lookup Sample\LookupSample\LookupSample.dtsx" *
- 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
- Open Business Intelligence Development Studio.
- 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.
- In Solution Explorer, right-click the SSIS Packages folder, and then click Add Existing Package.
- Add the LookupSample.dtsx package by doing the following steps:
- In the Package location list, select File System.
- Click the browse button (...), select the LookupSample.dtsx package, and then click OK.
- 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.
| Element || Purpose |
| Data Flow task|| The first DataFlow task, DFT Load Lookup Cache, loads the reference dataset from the DimTime.csv text file and uses a Cache Transform transformation to populate a Cache connection manager with the data. |
| Derived Column transformation|| The Derived Column transformation modifies the data before the Cache Transform transformation writes the data to the Cache connection manager. The Derived Column transformation applies expressions to existing columns to create two new columns. |
| Cache Transform transformation|| The Cache Transform transformation maps columns from the DimTim.csv text file and Derived Column transformation to columns in the Cache connection manager. |
| Data Flow task|| The second DataFlow task, DFT Load Fact Table, loads data from the FactBudget.csv text file. The task uses a Lookup transformation to perform lookups by joining data in the input columns with columns in the reference dataset. |
| Lookup transformation|| The Lookup transformation, Lookup DimTime, uses a Cache connection manager to access the reference dataset and to cache this dataset before the Lookup transformation runs.The Lookup transformation also directs rows to three Flat File destinations:|
- Rows with matching entries in the reference dataset are directed to the match output.
- Rows without matching entries are directed to the no match output.
- Rows with truncation errors are directed to the error output. |
| Row Count transformation|| The Row Count transformation counts the number of rows directed to each Flat File destination and stores the information in user-defined variables. |
| Cache connection manager|| The Cache connection manager stores, in-memory, the data that both data flows use. |
| Flat File destination|| The output from the Lookup transformation is stored in Flat File destinations. |
| Flat File connection managers|| The five flat file connection managers connect to the input and output text files. |
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
© 2008 Microsoft Corporation. All rights reserved.