What an SSIS Lookup Transformation and what new features did SQL Server 2008 deliver?

Often times you need to pull incremental data from a source database and merge it in a destination table. You can use the T-SQL Merge command to merge this delta data from your staging table to main table, if you store your data in staging table before load, or use the Lookup transformation which allows you to merge data on the fly, in-memory, to the destination table. 

The lookup transformation performs a join between the source and reference table/view. With SQL server 2008, this transformation has been further enhanced to output rows for matched records, rows for unmatched records and error output. Apart from that, the enhancement also includes the ability to cache your referenced data so that you can reuse it multiple times instead of querying it every time.