Lookup Transformation Task

Question

What is the main purpose of Lookup Transformation Task ?

Answer 1

SSIS lookup transformation task issue

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/412fae94-d09d-49b7-b6d0-2bb30b6852c5

Lookup Transformation

http://msdn.microsoft.com/en-us/library/ms141821.aspx

Answer 2

The main purpose of the Lookup Transformation is to use a reference value from your input data to retrieve a value (usually a foreign key identifier) from a lookup table. If your input data has a field that holds RegionName, for example, and in your destination database you have a region table that has RegionID and RegionName, you can use a lookup in your dataflow to use RegionName to lookup the RegionID from the lookup table and include its value in your dataflow for writing out to your destination table.

Answer 3

The lookup task is simply for looking up values from a table based on a provided lookup key(s). I provide an employee number and I can get back new fields into my data flow for name, departmnet, etc. This is the genric purpose it has. Now, the specific purpose of the Lookup Task is to be the key component in a surrogate key pipeline. When processing facts into a data warehouse, you need to replace the business/natural key with the surrogate key from the dimension table. The pipeline is a data flow task with lots of lookups to accomplish this. The lookup task also has the ability to direct lookup failures onto a seperate branch of the data flow to deal with late arriving dimensions / early arriving facts. In other words, if the lookup fails, you can substitute in some other value. In the case of late arriving dimensions, you can assign a surrogate key and put a place holder in the dimension table for the dimension that is late.