Lookup task in SSIS 2012 - using a view rather than a table?

Question

Lookup task in SSIS 2012 - using a view rather than a table?

ericjlawson

Mr or Mrs. 500

Points: 581
More actions
August 6, 2014 at 6:27 am

#397166

Hi,
We have a LKP table that we will use to decode incoming codes from multiple sales feeds to link to the relevant surrogate key for the ultimate dimension table.
The LKP table has a start date, end date and active row flag to track history of the codes.
This table in its entirety is required for the FULL history load but we will need a CURRENT version of the table containing only the ACTIVE rows for the DAILY incremental loading.
I am thinking of putting a standard view over the top of the LKP table to present only the ACTIVE rows rather than creating a 2nd table and reloading it each night. We don't have enterprise edition to use materialised views.
My question is, will the LKP cache populate as the SSIS packages begins, loading it with all the rows from the view, as it would if I used the 2nd physical table with only ACTIVE rows?
Thanks
Eric

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

ericjlawson (8/6/2014)
Hi,
We have a LKP table that we will use to decode incoming codes from multiple sales feeds to link to the relevant surrogate key for the ultimate dimension table.
The LKP table has a start date, end date and active row flag to track history of the codes.
This table in its entirety is required for the FULL history load but we will need a CURRENT version of the table containing only the ACTIVE rows for the DAILY incremental loading.
I am thinking of putting a standard view over the top of the LKP table to present only the ACTIVE rows rather than creating a 2nd table and reloading it each night. We don't have enterprise edition to use materialised views.
My question is, will the LKP cache populate as the SSIS packages begins, loading it with all the rows from the view, as it would if I used the 2nd physical table with only ACTIVE rows?
Thanks
Eric

If you don't change any of the defaults - yes the lookup transformation will cache the rows - no different to using a table.

Also, you can use a query in the lookup transformation if you want - the query would have the same logic in it as the view,

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

happycat59 (8/6/2014)
ericjlawson (8/6/2014)
Hi,
We have a LKP table that we will use to decode incoming codes from multiple sales feeds to link to the relevant surrogate key for the ultimate dimension table.
The LKP table has a start date, end date and active row flag to track history of the codes.
This table in its entirety is required for the FULL history load but we will need a CURRENT version of the table containing only the ACTIVE rows for the DAILY incremental loading.
I am thinking of putting a standard view over the top of the LKP table to present only the ACTIVE rows rather than creating a 2nd table and reloading it each night. We don't have enterprise edition to use materialised views.
My question is, will the LKP cache populate as the SSIS packages begins, loading it with all the rows from the view, as it would if I used the 2nd physical table with only ACTIVE rows?
Thanks
Eric
If you don't change any of the defaults - yes the lookup transformation will cache the rows - no different to using a table.
Also, you can use a query in the lookup transformation if you want - the query would have the same logic in it as the view,

+1 Don't use the dropdown box in the lookup component. Write a query and use only the columns you actually need.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ericjlawson Mr or Mrs. 500 Points: 581 More actions · Answer 3

Thanks. Will either put the SQL straight into the LKP or create a view and select that.

Thanks to both of your for your input.