CDC Related question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » CDC Related question

CDC Related question

Rate Topic

Display Mode

Topic Options

Author

Message

MJ123

Posted Friday, March 22, 2013 2:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 19, 2013 1:08 PM
Points: 2, Visits: 28

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

Alexander Suprun

Posted Friday, March 22, 2013 3:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:15 PM
Points: 160, Visits: 802

Google?

Alex Suprun

Post #1434528

opc.three

Posted Friday, March 22, 2013 3:39 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434535

MJ123

Posted Monday, March 25, 2013 7:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 19, 2013 1:08 PM
Points: 2, Visits: 28

I am using SQL Server 2008 R2 as a source and Oracle 11 G as target.

Post #1434910

opc.three

Posted Monday, March 25, 2013 8:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 6,720, Visits: 11,759

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1434935

« Prev Topic | Next Topic »

Permissions