How to: Implement a Lookup Using the Lookup Transformation
To add and configure a Lookup transformation, the package must already include at least one Data Flow task and one source. The Lookup transformation also requires that the package includes an OLE DB connection manager that is configured to connect to the database that contains the lookup data.
To implement a Lookup transformation
In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
Click the Data Flow tab, and then, from the Toolbox, drag the Lookup transformation to the design surface.
Connect the Lookup transformation to the data flow by dragging a connector—the green or red arrow—from a source or a previous transformation to the Lookup transformation.
Double-click the Lookup transformation.
In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:
- Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.
- Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.
Click the Columns tab.
On the Columns tab, drag at least one column from the Available Input Columns list to a column in the Available Lookup Column list.
Note
The Lookup transformation automatically maps columns that have the same name and the same data type.
Note
Columns must have matching data types to be mapped. For more information, see Integration Services Data Types.
To include columns from the lookup table, select columns from the Available Lookup Columns list to include in the transformation output, and in Lookup Operation list, specify whether the values from the lookup table replace values in the input column or are written to a new column.
To configure the partial caching of the lookup table, click the Advanced tab, and do the following:
- Select Enable memoryrestriction.
- Select Enable caching to specify the cache size or select Modify the SQL statement to update the SQL statement that generates the lookup table, or select both options.
- Optionally, click Parameters and map parameters to different input columns.
To configure the error output, click Configure Error Output. For more information, see How to: Configure an Error Output in a Data Flow Component.
Click OK.
To save the updated package, click Save Selected Items on the File menu.
See Also
Concepts
Lookup Transformation
Integration Services Transformations
Integration Services Paths
Data Flow Task
Connection Managers
Data Flow How-to Topics (SSIS)
Other Resources
Integration Services Sources
Integration Services Destinations