Lookup (SSIS)

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset.

You can configure the Lookup transformation in the following ways:

• Select the connection manager that you want to use. If you want to connect to a database, select an OLE DB connection manager. If you want to connect to a cache file, select a Cache connection manager.
• Specify the table or view that contains the reference dataset.
• Generate a reference dataset by specifying an SQL statement.
• Specify joins between the input and the reference dataset.
• Add columns from the reference dataset to the Lookup transformation output.
• Configure the caching options.

The Lookup transformation supports the following database providers for the OLE DB connection manager:

• SQL Server
• Oracle
• DB2

• If there is no matching entry in the reference dataset, no join occurs. By default, the Lookup transformation treats rows without matching entries as errors. However, you can configure the Lookup transformation to redirect such rows to a no match output. For more information, see Lookup Transformation Editor (General Page) and Lookup Transformation Editor (Error Output Page).
• If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query. If multiple matches are found, the Lookup transformation generates an error or warning only when the transformation has been configured to load all the reference dataset into the cache. In this case, the Lookup transformation generates a warning when the transformation detects multiple matches as the transformation fills the cache. 

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