In SQL Server Integration Services (SSIS) one of the most frequently used transforms is the Lookup Transform. One of the shortfalls with the transform is the apparant lack of ways to make the lookup cache dynamic. In this article, I'll show to two ways to make the cache dynamic for SQL Server 2008.
Let's start with making a data flow properties dynamic. To make the lookup cache dynamic there, you must apply a bit of a unusual workaround. First, configure your lookup cache to use a query as shown in the below screenshot. There's nothing new here but it's important to note that you only want to cache columns that you care about. Otherwise, you'll spend much more time at runtime loading the cache and potentially run out of memory. The query you see here has not been parameterized yet and this query will only act as a stub query.
Next, we need to create a few variables. The first variable is going to hold how you want to slice the data. In my case, the variable is called sOccupation and will hold the occupation that we want to load into cache. This could be a client number or a start date. The other (I've called it sQuery) variable is going to hold the query that will replace the earlier mentioned Lookup Transform cache query. Once you create the package-scoped variable called sQuery, set the EvaluateAsExpression variable property to True. This enables you to make the variable dynamic based on an expression. We can blend the two variables together by using an expression like this:
"select convert(varchar(10), CustomerAlternateKey) as CustomerAlternateKey, CustomerKey from dimcustomer WHERE EnglishOccupation = '" + @[User::sOccupation] + "'"
Once the variables are created, you're now ready to tie the SQL query to your Lookup Transform. Select the Data Flow Task that has your Lookup Transform in it and notice in the Properties Window (while the Data Flow task is selected), there's a property for your Lookup Transform called [Lookup Transform Name].[SqlCommand]. You can tie an expression to this property to make your cache query dynamic in full cache mode. The SQLCommandParam property is used for partial caching and won't apply by default.
The last step is to simply select the Data Flow Task and go into the Expressions option. Once there tie the [Lookup Transform Name].[SqlCommand] to the sQuery variable and whalla! You're all done with a single flaw. The flaw is that you cannot rename the Lookup Transform without causing downstream pain with this expression. It's a small price to pay though.
In SQL Server 2008, the Lookup Transform has had a huge overhaul, making it much simplier to use and resuse the lookup cache; even dynamically. To start, create an OLE DB Source and tie the query that's used for the source to the earlier created sQuery variable. Tie this source to the Cache Transform. The Cache Transform is a new SQL Server 2008 transform that can allow you to use a cache file over and over again. One of the great uses for it is to have the ability to cache a customer dimension during the day and then use it over and over again across multiple packages. The cache file it creates resembles a raw file on disk and is quite fast. If you chose to not check "Use File", the file is still created and destroyed after the fact transparently to you. This file is the only way to get around memory limits you may have though.
Once you open the Cache Transform, create a new connection manager. Name the connection manager and if you check Use File Cache, the file will remain until you either reload it or delete it using a File System Task. In the Columns tab, you'll see a listing of columns from the OLE DB Source (since they're connected). The column that you plan on joining should be set to an Index Position of 1. If you have a concatenated key, you can have an index column of 1, 2, etc.
Click OK and go to your Mappings tab to map the source to the cache. Notice that back in the data flow, you also have a green line coming out of the Cache Transform. Technically, you could immediately go right into a Lookup Transform and use the cache file. You can alternativily use the cache connection manager from a different data flow or if you choose to keep the file, you can use it from a different package or server even. To ue the file, simply go into your Lookup Transform and in the General tab, select "Cache connection manager" and point to the newly created connection.
In this article, you were shown how to use a bit of a funky workaround to get around cache woes by modifying the expression on the Data Flow Task. You were also shown an easier way to get around memory limits in SQL Server 2008 with the Cache Transform. This transform also makes it easy to cache dynamic queries by just switching out your source query with a variable.