Using SSIS Lookup Transformation in ETL Packages

Lookup is one of many transformations available in SQL Server Integration Services (SSIS). Its purpose is to perform a JOIN of two datasets: input columns from a data source and columns in a reference dataset – a table, view or a result of a query. Lookup can be used in various scenarios but this post focuses on how to use it to implement a typical ETL package that performs incremental loads into a Data Warehouse.

Here’s a simplified description of how Lookup works: it tries to match each incoming record with one or more records in the reference table. If there is a match, the incoming record is sent to the match output. If there is no match, Lookup generates an error by default but it can also be configured to send the data to the no match output. There are some more considerations that will be described later on. Continue reading