August 19, 2008 at 9:51 am
Well, so far you are on the right track.
Deletes:
You would have to use a OLEDB Command for that. Depending on number of rows (How many are the BTW) this may end up hurting you. I will explain a different way if it is large.
For the insert, you should use a lookup component. In the Advanced tab, enable Memory Restriction. This forces it to look for each row as it arrives instead of caching it all before hand. Again, large sets will perform slowly. There are other ways.
Question though: You say there are inserts without deletes. How do you ever get new data? First time records - would they still have a delete?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 11:19 am
We initially receive a historic file and then we get daily updates. The historic load was simple. I just noticed that not all of the updates had delete records before the inserts like there were supposed to be. I spoke with our provider and he just informed me that they now have some instances where they will not provide a delete before the update or insert and that we should account for that. So, my plan is to generate a delete table with the record ids that need to be deleted as regular deletes (as well as deletes for the updates and new inserts). Remove them from the existing tables and then import the inserts and updates as new records following the same process I was following.
August 19, 2008 at 12:50 pm
There could be around 50,000 differenct record ID's to delete which could be 4 or 5 records in some tables.
August 20, 2008 at 1:15 am
With that amount of rows, I would load all the delete ID's into a table using a dataflow, then using a sql task, I would run a SQL script that joins the actual table to the tempdelete one and does the delete. The same would apply for the inserts.
This method would be far far quicker than row by row.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 20, 2008 at 8:34 am
That's what I did. I used the execute sql task, added the connection and added an SQL statement like delete from tableA where id in (select id from deleteTable). I then do the inserts the way I was doing them (import them from the flat file into their correct tables).
August 20, 2008 at 8:37 am
Much better option.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 20, 2008 at 11:45 am
What would be involved in using a lookup transformation to insert new rows and update existing rows?
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply