Lookup

Overview

Lookup Plus Component is SSIS Data Flow Component providing enhanced functionality 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 variety of connection types as a source, making it quite versatile.

Setup

Use the options below to setup Lookup Plus component for processing.

Options

Name

Specify component name.

Description

Specify component description.

Lookup definition

The lookup definitions are setup in the lookup definition grid.

Input Column

Select input column for lookup key.

Reference

Setup lookup reference.

Related Topics: Reference Setup

Result Column.

Specify lookup result output column name.

On NULL

Specify output value when input is undefined.

No Match

Specify output value when input doesn't produce 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 lookup source. Three source types are available: table or view, SQL query and package variable.

ConnectionType

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

Value	Description
OLEDB	Use OLEDB Connection Manager for Connection option.
ADO	Use ADO Connection Manager for Connection option.
ADO.NET	Use ADO.NET Connection Manager for Connection option.
CACHE+	Use Distributed Cache Connection Manager for 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 lookup source.

SQL Query radio button

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

From Variable radio button

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

Value	Description
String	Variable contains SQL statement. The first selected column will be used as key and the second selected column will be used as 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 key and the second selected column will be used as value.
DataSet	ADO.NET DataSet object. It will use the first table. The first selected column will be used as key and the second selected column will be used as value.

Key Column

Select lookup source key column.

Value Column

Select lookup source value column.

Match multiple columns with Lookup Plus

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

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

Samples

• For a sample how to use a custom-crafted range dictionary object from variable, check this package. The range dictionary object is constructed with this script.