March 22, 2013 at 2:24 pm
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....
Thank you
MJ
March 22, 2013 at 3:39 pm
What version of SSIS are you using? What is the version of SQL Server where the data source is hosted?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2013 at 7:40 am
I am using SQL Server 2008 R2 as a source and Oracle 11 G as target.
March 25, 2013 at 8:10 am
Bummer. SSIS 2012 has some new Tasks built in to make working with CDC data a breeze, but it's not too difficult with SSIS 2008 R2. Have a look at this article:
Improving Incremental Loads with Change Data Capture - SQL Server 2008 R2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply