Reusing lookup transforms

• ducon

  Hall of Fame

  Points: 3801

  More actions

  December 10, 2009 at 9:43 am

  #136845

  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

• COZYROC

  One Orange Chip

  Points: 28499

  More actions

  December 10, 2009 at 8:36 pm

  #1090969

  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/

  

• ducon

  Hall of Fame

  Points: 3801

  More actions

  December 10, 2009 at 9:12 pm

  #1090976

  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