Pages

cross

Sunday, November 29, 2015

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.