April 29, 2020 at 5:14 pm
Hi,
I am importing data from csv flat file with 481 278 records to my database table with 199 998 records already. This 199 998 already in the table are part of the 481 278 meaning i want to load the difference of (481 278 - 199 998 = 281 280).I am using Lookup in full cache mode but to my surprise SSIS is not writting the difference of 281 280 to the table, only 2100 records are written. I am confused why is this happening.
I have tried Partial and No cache but still no help.
Please help.
Thank you
April 29, 2020 at 5:51 pm
We cannot easily help with this, based on what you have posted.
Are you using a unique identifier in the lookup? Is it unique in both the source file and the target table? If yes, full cache mode should work fine (and be the fastest option).
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
April 29, 2020 at 9:38 pm
I would not use a lookup here - I would load the full file into a staging table. From there you can perform a merge or upsert (update/insert) from the staging table to the final table. If this is truly just adding the missing rows then you just need an insert statement from the staging table and a NOT EXISTS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2020 at 9:41 pm
I would not use a lookup here - I would load the full file into a staging table. From there you can perform a merge or upsert (update/insert) from the staging table to the final table. If this is truly just adding the missing rows then you just need an insert statement from the staging table and a NOT EXISTS.
If it is a direct insert of rows which currently don't exist in the target, a lookup is going to be the fastest-performing solution and it should work just fine without the overhead of populating a staging table.
If some sort of upsert is required, that changes everything and I agree with you.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply