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?