Hello All,

How do I know when its the right time to use the Cache Connection Manager?  I understand that Full Cache mode eliminates the need to query the lookup table for every row.  (Please correct me if I'm wrong!)  Is the Cache Connection Manager intended for use by multiple packages that need access to the same lookup data?

Thanks,
Eric

Hi Eric,

The lookups (fuzzy, or regular) actually are the ones to use against the Cache.

Regardless of the mode (partial or full) the lookups do not go against the database. It will bring the needed data.

You can share the same cache, it is yet another plus to using it.

You can (I did, too) warm the cache up by a package running ahead of the consumers, then several others would use it (the same cache) later. This removes the strain from the data source and allows faster processing.

Now the modes: full/none/partial is for performance considerations, if the machine has enough RAM to hold the entire dataset, then make it 'full' - the fastest possible, otherwise partial.

The cache connection manager reads data from cache transformation or from a cache files (.caw) and can save the data to a cache file. Using the Cache Connection Manager (CCM) is a new option for the Lookup transform in SSIS 2008.

The CCM provides an alternative to doing lookups against a database table using an OLEDB connection. Whether you configure the cache connection manager to use a cache file, the data is always stored in memory.

The cache connection manager does not support the Binary large Object (BLOB) data type DT_TEXT, DT_NTEXT and DT_IMAGE.

Thanks, but when is the right time to use the cache connection manager?

The Cache transform 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. 

 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.