Readme_Lookup Transformation Sample

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



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.

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 CreateExecuteProcess_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:
  5. In the Package location list, select File System.
  6. Click the browse button (...), select the LookupSample.dtsx package, and then click OK.
  7. 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.


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
© 2008 Microsoft Corporation. All rights reserved.

Last edited Feb 16, 2009 at 9:44 PM by sabottaca, version 6

Comments

torechia Aug 15, 2008 at 2:11 PM 
Does this sample work with sql server 2005? I tried to include this package to an existing sql server 2005 project, but am getting error "Package failed to load...CPackage::LoadFromXml fails". I also do not see a Data FLow Transformatiion for Cache Transform Transformation in the toolbox on sql server 2005 so I cannot attempt to build my own package. Is this package able to run or able to be built on sql server 2005?