Reusing lookup transforms

  • Hi there,

    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,

    Greg

  • ducon (12/10/2009)


    Hi there,

    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,

    Greg

    Greg,

    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/

  • Hi there,

    Thanks for the reply. If I wanted I could easily code one in .NET and either package it as a custom component or maybe even use directly the script transform to achieve that goal but I want to stay as close to the original components provided by Microsoft for distribution and ease of licensing purposes.

    Thanks for the info though, I will check out these components anyway 🙂

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply