SQL Server Advent Calendar 14 – Integration Services Lookups

Day 14 of my virtual advent calendar, about stuff I like in SQL Server 2008..

If it aint broke don’t fix it, is probably best aimed at Integration Services in SQL Server 2008. So what was ‘fixed’?  The most important thing was the performance of lookups, both in terms of speed and memory usage.  To address this there is a new lookup cache feature to allow frequent lookups from static data to be persisted to a special file (*.caw) .

This cache need to be populated from the new cache transform task in the data flow designer:

image

To use this you need to tell the inevitable wizard what connection, table and columns you want to cache…

image

Note the 1 in the index position for the SalesTerritoryAlternateKey row to indicate this is indexed (at least one column must be indexed).

So that’s how you populate the cache, now it can be used in the standard lookup transformation by selecting cache connection manager on the general tab for the lookup

image

and then putting in the cache location on the connection tab (You will be warned to do this). You can then use the columns form the cache in the same way as for a normal lookup. As ever I’ve done this in AdventureWorks so you should be able to follow this.

This is going to be useful in applying the same (conformed) dimension to multiple fact tables, particularly if the dimension doesn’t change that often.  I would also point out that there has been a lot of work behind the scenes to improve the parallelism in integration services, which will kick in when you upgrade.

Technorati Tags: SQL SErver 2008,Integration services,persistent lookup