dynamic lookup

Question

dynamic lookup

sql_learner29

SSCrazy Eights

Points: 9107
More actions
July 26, 2010 at 2:11 pm

#235696

Hi all,
I have a requirement to update rows and lookup on updated rows, can this be done using SSIS , kind of dynamic lookup , I am using SSIS 2008...
thanks
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Please provide more detail - your requirement is difficult to understand. An example would help.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Hi,

Here is the requirement in detail.

I have to lookup a table address and I am adding addresses in the same DATA FLOW TASK, so when the address is added for an account number, and the same account number comes again in the flow from my source ,the lookup should know that it is there now...

and should be able to look ip up now, and not add again an address.

Hope its clear.

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

So your DF contains duplicates? Why not get rid of them early in the DF and save yourself the trouble? If not, what happens when the lookup is successful? How is your source data ordered?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

Hey Phil,

Actually the requirement is to lookup the updated rows...and dont update them if they are already updated, but since lookup cache the rows, I dont think this can happen, I will have to find some other way to do this....

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

ameda16 Valued Member Points: 68 More actions · Answer 5

an idea: use no cashing instead of full precaching, the default caching type

No caching, in which the reference dataset is accessed by each row in the rowset. To configure a Lookup transformation to use no caching, on the Advanced tab of the Lookup Transformation Editor, select Enable memory restriction and clear all other options .

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

ameda16 (7/27/2010)
an idea: use no cashing instead of full precaching, the default caching type
No caching, in which the reference dataset is accessed by each row in the rowset. To configure a Lookup transformation to use no caching, on the Advanced tab of the Lookup Transformation Editor, select Enable memory restriction and clear all other options .

While that sounds that it might work, it also sounds very inefficient! Maybe the poster should consider using a staging table so that this can all be done using set-based updates.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

I agree that the no-caching solution would be terribly inefficient.

I also think we need to remember rows in the pipeline that haven't been into the later stages of the process.

A row in the same internal batch that has dupes will not be detected because when the lookup occurs the updates have not been written yet. This may not be an issue in what you are describing but it is something to be aware of.

A more set based operation would be much more efficient.

CEWII

hemani2000 SSC-Addicted Points: 427 More actions · Answer 8

Hi,

I am also having the same issue where I have to lookup the updated rows...and don't update them if they are already updated. I know I can't use No Cache but would like to do that in the SSIS package. Any help will be appreciated.

Thanks.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 9

hemani2000 (6/29/2011)
Hi,
-- where I have to lookup the updated rows...and don't update them if they are already updated. --

Please try re-reading this & maybe you'll realise why I don't know what you mean 🙂

Can you provide an example to illustrate?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

hemani2000 SSC-Addicted Points: 427 More actions · Answer 10

I have 2 tables Staging and Master. I would like to add a new row in the Master if the data from staging table does not exists in Master. Staging table might have duplicate data, so I don't want to add the duplicate data in the Master.

I have 2 lookups, First on Id, FirstName_Soundex and LastName_Soudex and if there are records with no match then another lookup on LastName and FirstName.

Staging Table : Id, FirstName,FirstName_Soundex, LastName and LastName_Soundex

Master Table : Id, FirstName,FirstName_Soundex, LastName and LastName_Soundex

My main question is how can I avoid adding duplicate data into Master from Staging? and I don't want to remove duplicate records from Staging.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

Let me see if I understand. Your staging table is either a superset or roughly the same as the master table correct? the staging table can have duplicates. Are the duplicates EXACTLY the same? If not how do you choose the right one?

Also, are you sure it is 2005 because the no match output is not available in 2005, it was added in 2008 if I remember correctly. Also does your process do updates or just inserts?

CEWII