I have a requirement to transfer data from SQL CDC table into the oracle database in some intervals(every hours or so). CDC table capture DMLs 24x7.I have developed SSIS package to do this. I have some problem in it. I want to delete only those records from source CDC tables which have been transferred to Oracle, keeping other records untouched(since they didn't flown to oracle yet) . I dont want to user for loop in SSIS package and transfer 1 records at a time. Instead,I want transfer records in bulk.
Is __$seqval column in CDC table has particular sequence? can i use it for tracking purpose? It show some binary data though..e.g. 0x01F195C5000000B40003
I am thinking of keeping __$seqval of last available record from CDC table into some tracking table, Once transfer of bulk data in done and I will delete data up to that __$seqval (from tracking table). In that way i won't loose any record as there could be DMLs done during transfer process as well. Or Is there any better way of achieve this...like using any other columns/tables from CDC schema etc..
Any suggestion would be very helpful....