Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

The Lookup is a fairly popular transformation. Its reason is that using this, reference attributes can be more efficiently joined to the data flow than with a join operation. The Lookup is also useful for identifying non-existing data in the input data set (comparing to the existing data).

There are major differences between a lookup and a join operation however.
First, lookup searches for the first match, omitting the rest - a join might return more records than the input dataset. Lookup will only have one output row for an input row.
Second, a lookup operation performs an equi-join (checks only equality), and always case sensitive. Case sensitivity is not an issue of course when reference data is joined using only numeric columns, but it's good to remember this when the join key has at least one alphanumeric column.

If no match is found in the reference dataset for an input row, then (by default) the Lookup transformation returns an error.
There are four options to treat non-matching input rows:

  • Leaving the Lookup as it is - the transformation fails if there is an input row with no matching reference data
  • Redirecting rows to the error output
  • Sending all these records to the No Match output (which doesn't include the reference columns)
  • Ignoring the failure, and using NULLs in the reference columns (sending rows to the Match Output)

So, the Lookup transformation supports an input, a Match and a No Match output, and an error output. This is how it looks like in SSDT:

The Lookup is a row (synchronous) transformation.

The Lookup basically puts all reference data to memory (caches it) - however this behavior can be overridden if necessary. For options of persisting and other options of the cache, read this MSDN article.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.