SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CDC Related question


CDC Related question

Author
Message
MJ123
MJ123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 1516
Google?


Alex Suprun
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14905 Visits: 14396
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
MJ123
MJ123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 36
I am using SQL Server 2008 R2 as a source and Oracle 11 G as target.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14905 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search