Lookup

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Join VIP ----- Questions?

Overview

The Lookup Plus Component is an SSIS Data Flow Component providing enhanced functionality as compared to the standard SSIS Lookup component. To simplify your data flows, the component allows multiple lookup definitions in the same dialog and definition of values when the input cannot be matched or when it is undefined. In addition the Lookup Plus component supports a variety of connection types as a source, making it quite versatile.

Demonstration

Demonstration VideoDemonstration Video

Setup

Use the parameters below to set up the Lookup Plus component for processing.

Parameters

Name

Specify the component name.

Description

Specify the component description.

Lookup definition

The lookup definitions are set up in the lookup definition grid.


Input Column

Select input column for lookup key.

Reference

Set up lookup reference.

Related Topics: Reference Setup

Result Column.

Specify the lookup result output column name.

On NULL

Specify the output value when the input is undefined.

No Match

Specify the output value when the input doesn't produce a match.

Refresh

Use the refresh command to update the lookup definitions with the latest metadata.

Reference Setup

The reference setup dialog is used to create the lookup source. Three source types are available: table or view, SQL query and package variable.


ConnectionType

Select the connection type. This parameter has the options listed in the following table.

Value Description
OLEDB Use the OLEDB Connection Manager for the Connection option.
ADO Use the ADO Connection Manager for the Connection option.
ADO.NET Use the ADO.NET Connection Manager for the Connection option.
CACHE+ Use the Distributed Cache Connection Manager for the Connection option. (1.5 SR-1)
Connection

Select an existing connection manager.

New

Click New to create a connection manager.

Related Topics: OLE DB Connection Manager, ADO Connection Manager, ADO.NET Connection Manager

Table / View radio button

Select an existing table or view as the lookup source.

SQL Query radio button

Specify an SQL statement as the lookup source. Use (1.5 SR-1) to create an SSIS expression.

From Variable radio button

Select an existing package variable as the lookup source. Supported object types are as follows:

Value Description
String A variable contains an SQL statement. The first selected column will be used as the key and the second selected column will be used as the value.
IDictionary Any object implementing this interface.

Related Topics: Dictionary Destination script, Range Dictionary Destination script.
Recordset ADO Recordset object. The first selected column will be used as the key and the second selected column will be used as the value.
DataSet ADO.NET DataSet object. It will use the first table. The first selected column will be used as the key and the second selected column will be used as the value.
Key Column

Select the lookup source key column.

Value Column

Select the lookup source value column.

Match multiple columns with Lookup Plus

You can match multiple columns using the Lookup Plus component. See the steps below for one possible solution:

  1. Create a new data flow. This data flow will build a lookup reference dictionary and store it in a package variable.
  2. Insert a data flow source component.
  3. Insert the Checksum Transform script after the component from step 2 to create a unique CRC32 value for the match input columns.
  4. Insert the Dictionary Destination script after the component from step 3. For the key, select the input column containing the CRC32 value. For the value, specify the appropriate reference column. Store the resulting dictionary object in a package variable.
  5. Create another data flow, which will do the lookup and matching.
  6. Insert the data flow source with the data that is to be matched.
  7. Insert the Checksum Transform script after the component from step 6 to create a unique CRC32 value for the required input columns.
  8. Insert the Lookup Plus component. As the key, specify the input column containing the CRC32 value. In the reference dialog, select the "From Variable" option. Set it to the variable you created in step 4.

SQL 2012 compatibility and Upgrade

Existing packages containing Lookup Plus component and designed with SSIS+ 1.5 or older version are incompatible with SQL 2012. To have successful upgrade follow these steps:

  • Install SSIS+ 1.6 or newer on a machine where you have existing packages designed with SQL 2005 or 2008.
  • Open the existing package containing Lookup Plus component and re-save.

The saved package will now contain SQL 2012 compatible data and the package is ready for upgrade.

Related scripts

Samples

  • For a sample showing how to use a range dictionary object from a variable, see this package.