In summary, the functional job of a lookup is to facilitate the combining of data from 2 data sources by matching one or more of the fields. Many different vendors offer various techniques and functionality in the lookup transformation offered by their product. In general a lookup transformation produces an output that is equivalent to an inner join between the lookup's source and the incoming data. Depending on whether a match is found during the processing, most vendors produce an output stream of matched values, an output stream of missed values, or raise an error condition.
SQL Server 2005 (SSIS)
SQL Server 2005 offers a fairly basic implementation of a lookup transformation.
