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

CDC Related question Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 2:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 2:13 AM
Points: 3, Visits: 36
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
Post #1434513
Posted Friday, March 22, 2013 3:21 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:02 AM
Points: 217, Visits: 1,010
Google?


Alex Suprun
Post #1434528
Posted Friday, March 22, 2013 3:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1434535
Posted Monday, March 25, 2013 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 2:13 AM
Points: 3, Visits: 36
I am using SQL Server 2008 R2 as a source and Oracle 11 G as target.
Post #1434910
Posted Monday, March 25, 2013 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1434935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse