Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reusing lookup transforms Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:19 PM
Points: 122, Visits: 273
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
Post #832368
Posted Thursday, December 10, 2009 8:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 1,118, Visits: 2,224
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/

Post #832694
Posted Thursday, December 10, 2009 9:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:19 PM
Points: 122, Visits: 273
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
Post #832702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse