Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert / Deletes Expand / Collapse
Author
Message
Posted Tuesday, August 19, 2008 9:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
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?
Post #555125
Posted Tuesday, August 19, 2008 9:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #555137
Posted Tuesday, August 19, 2008 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
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.
Post #555212
Posted Tuesday, August 19, 2008 12:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
There could be around 50,000 differenct record ID's to delete which could be 4 or 5 records in some tables.
Post #555276
Posted Wednesday, August 20, 2008 1:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
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,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #555557
Posted Wednesday, August 20, 2008 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
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).
Post #555815
Posted Wednesday, August 20, 2008 8:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
Much better option.




Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #555818
Posted Wednesday, August 20, 2008 11:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 2:02 PM
Points: 37, Visits: 114
What would be involved in using a lookup transformation to insert new rows and update existing rows?
Post #555995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse