August 19, 2008 at 9:42 am
I am working with a sequential file that is supposed to have all deletes at the top of each section of the file. The rules are supposed to be followed as process deletes and then run the inserts. Which make sense, any updates would simply be delete the original record, and add the new record with the update. Here's the problem, we get the data from a third party and I have noticed that not all updates have a preceding delete. So, that makes me think that I need to now not trust their deletes and simply check to see if each recordid exists in the destination tables and if so delete the record before inserting the record from the source.
I currently have a flat file data source, a script component to parse the record id and record type out. Then a conditional split to process each record type and a script component that parses the rest of the row into the correct columns. I am guessing that between the last script component and the oledb destination should be some method of looking up the record and either delete or insert as needed.
SSIS is not something that I work with everyday, as I am a C# developer, and I am sure there are probably some simple solutions out ther. Any ideas?
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply