The lookup transform can utilize this cached data to perform the lookup operation. The cache connection manager 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 useful is in the extract, transform and Load (ETL) packages where when we are processing the fact table we have to lookup to to application vs surrogate key. In my datawarehouse projects I call this the Lookup table (LU)). So if we create a lookup cache per dimension, the time of loading of the fact will be decreased when we use this features. It will even speed up more when a dimension (cache) is used by multiple facts.
To demonstrate how to use the new lookup and cache transforms, we will create a simple SSIS package a s shown below:
1. The first thing you have to do is create cache connection manager. Right click in the connection manager area of the control flow design surface and selkect new connection. Select CACHE from the add SSIS connection manager dialog and then click on the Add button
2. The next thing is to set some properties that need to be configured. On the general tab click the Use file cache checkbox and enter a filename for this cache; this will persist the cache to a local file, allowing it to be reused by other tasks in the package or other packages.
3. Click on the columns tab to configure the data columns to be stored in the cache.
4. Build the customer cache (DFT_CacheCustomer)
5. The OLE DB Source selects the customer data : SELECT CustomerID, AccountNumber FROM Sales.Customer.
6. Then setup the cache transform component. This one loads the cache . The cache transform requires a cache connection manager which defines the cache. You could create a new onebut we already created on in the connection managers pane.
7. Click on mappings to map the input columns in the dataflow to the destination columns in the cache.
8. From a former post you can recognize the following SSIS snippet
10. The next thing you have to select is the Cache connection:
Conclusion: As is already said in the intro, this feature can speed up a lookup function of loading a fact and especially dimension surrogate keys. Even more when the cache is used multiple times.