I have a question regarding the lookup transform. I know there is a disk cache available in SSIS 2008 but I'm looking into something in SSIS 2005.
I have a fact table that I am loading and for which I need to lookup multiple date surrogate keys (e.g. scheduled date, arrived date etc.). right now, the way I do this that I have one lookup transform in my dataflow per date. What I'm wondering is whether SSIS is smart enough to realize that I have 3 lookups but that they use the same SQL command so at run-time, to only execute my query once (I haven't profiled this but I'm gonna go on a limb here and say that it does not). Or, whether there is a way to make a query be executed once and used in multiple transforms, just using different mappings so that I can query my date dimension once and lookup values for my different dates. In application programming, this is very easy to do, load the dataset once and query it in memory for multiple values.
Thanks in advance,
There is no such feature in SQL 2005 and that's why they did implement the cache manager and support in SQL 2008.
If you can use third-party solutions, I would recommend you check the commercial CozyRoc Lookup Plus
component. The component has these extra features compared to the standard Lookup:
- ability to define multiple lookups in one place
- ability to define default value for NULL and No Match inputs.
- ability to use OLEDB, ADO, ADO.NET as a source
- ability to use dictionary object from variable as a source
The fourth feature provides similar benefit to the cache manager in the SQL 2008 Lookup and allows you to reuse your lookup information in multiple places in your workflow.
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/