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.

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.

From Variable radio button

Select an existing package variable as lookup source.

Note: When variable is of type String, the component will treat the variable value as SQL statement. Key Column and Value Column have to be provided. When variable is of type Object, the component will expect variable object to support IDictionary interface. You can use this feature to cache and reuse one and the same lookup in multiple places. Key Column and Value Column are not needed.

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.