SSIS - Can you use a parameterised query to populate a Lookup Transformation ?

Question

I am using a stored procedure to populate a Lookup Transformation, but would like to restrict the size of the lookup cache.

I could do this by supplying a parameter to the stored procedure, but I can't see any way of parameterising the query on the Lookup Transformation componenent.

Any ideas would be welcome!

Answer 1

If you are using SQL Server 2008, then you can use the new Cache Transformation. It can be populated from an OLE DB Source (which you can, of course, parameterize), and can either keep its rows in memory for use by one or more Lookup transformations, or can save the cached rows in a file, where they can be used by several different Lookup transformations, even in different packages.

See How to: Implement a Lookup Transformation in Full Cache Mode (SQL Server Video). It's only nine minutes, but shows the essentials.