Cache Transform

The Cache transform is a brand new feature in SQL 2008, allowing you to cache the data used in the Lookup transform.  The Lookup transform can then utilize this cached data to perform the lookup operation.  Working with cached data will most likely be more efficient than querying the database.  In addition the Cache Connection Manager (another new feature) can persist the cache to a local file, allowing the cache to be shared between multiple SSIS packages and steps within a package.  A perfect example of where this will be useful is in the extract, transform and load (ETL) packages that we create to update a data warehouse.  We typically build dimension tables in the data warehouse that use a surrogate key as a primary key (in lieu of the source key in the business application).  As we are processing our fact tables we need to lookup the dimension surrogate keys based on the key in our source system and store the surrogate key in our fact tables.