SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reusing lookup transforms


Reusing lookup transforms

Author
Message
ducon
ducon
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 286
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
CozyRoc
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11164 Visits: 2235
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
ducon
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 286
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 Smile

Greg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search