Home > SSIS > Lookup Transformation

Lookup Transformation


Searching a reference key value to fetch the related data from another dataset is a common database manipulation.

Lookup Transformation

Lookup Transformation task lookups input column with the reference dataset. Lookup transformation needs separate datasource connection. It may be a ole db connection (Oracle, DB2, etc) or a cache connection manager. Cache transformation and cache connection manager is explained in my previous post.

Lookup transformation takes one input pipeline and delivers Matching column output, Not matched column output and Error output. These output pipeline is used to redirect rows from the lookup transformation.

How to use Lookup Transformation?

1. Connect valid data source with key column used fetch the data from the lookup dataset.

2. Configure Lookup task with a ole db data source or cache connection. This is the lookup dataset connection.

3. Connect lookup output pipleline to the corresponding destination. Matched, Not matched, Error.

Scenario

I will be using adventure works database. Sales.Customer is the input data flow and Sales.Customer is the reference dataset.

I am going to get the AddressID from the reference data using customerid column as a join key column between source and the reference table.

Implementation

Step 1: Create package and Add a Data Flow task

Step 2: Go to Data Flow design interface and Add a ADO.Net data source with configuration to Adventureworks database Sales.Customer table.

Step 3: Add a Lookup transformation task

1. Open Lookup Transformation editor

2. In general Tab, Select ole db for the connection type and select redirect to no match output option under how to handle no match rows.

Step 4: Go to Connection tab and select the ole db connection and the reference table.

Step 5: Go to Column tab and map the source table key column Customerid with the reference table Customerid and select AddressID column. Now, AddressID column will be added to the data flow.

Step 5: Add two union all task and map match and no match output pipeline to each union all task

Step 6: Execute package. A data viewer component has been added between lookup task and the match output union all task to view the result data set.

MSDN Reference

Thanks for reading

Advertisements
  1. Yan
    August 20, 2014 at 4:37 pm

    I don’t understand here, by using ADO.NET source, how can you still use OLEDB connection in lookup transformation?

    • August 24, 2014 at 7:05 pm

      We can use two data connection for the same database ie. ADO.net, OLEDB. Now, we can use oledb in lookup transformation.

      • Yan
        August 25, 2014 at 9:03 am

        Cool! That works! Thank you very much!!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: