Results 1 to 2 of 2

Thread: Excluding the Delete operation from CDC tasks

  1. #1
    JasonP is offline Junior Member
    Join Date
    Feb 2011
    Posts
    5
    Rep Power
    0

    Excluding the Delete operation from CDC tasks

    I have searched through the forum and documentation and have not been able to find the aswer to this question. We plan to archive data out of our source Oracle db and do not want the archived data removed from the destination SQL Server db.

    So, how can I exclude the DELETE operation from my CDC tasks so that thet only pick up the INSERT and UPDATE operations?

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    The supported way would be to add a conditional split in the CDC package data flow between the CDC source and the CDC destination, filtering out rows where the 'operation' column is 'DELETE'.

    Note that Oracle may sometimes record an UPDATE operation as a pair of DELETE+INSERT so if you filter out the DELETE then you need to be able to handle the case of duplicate on insert and treat it as update.

    The method discussed above maybe somewhat lengthy if you have many tables. A less supported way is to block the capture of the DELETE records by modifying the underlying solution file CDC_AGENT.adp found at the path:

    solution-path\CDC\AIS\machines\SRV\adapterDefs\CDC_AGENT.ad p

    Save a copy of that file in a save place (not under the CDC\AIS tree). Then by adding the filter element as shown below:

    HTML Code:
    <?xml version="1.0" encoding="utf-8"?>
    <navobj revision="1">
      <adapterDef name="CDC_AGENT" type="oraclelCDCA">
        <interaction name="eventStream" mode="async-send">
          <interactionSpec startTimestamp="current time">
            <capturedTable name="Table1" />
            <capturedTable name="Table2" />
            <filters>
              <operationFilter tableName="Table1" operations="DELETE"/>
              <operationFilter tableName="Table2" operations="DELETE"/>
            </filters>
          </interactionSpec>
        </interaction>
        <schema />
      </adapterDef>
    </navobj>
    At this point, you will need to re-deploy the solution to the service so that the new definition would take hold.

    The CDC agent will drop the DELETE operations from the CDC source so you will not need to add the filters in the SSIS package.

    Note that the filters element is not maintained by the CDC Service wizard (it does not touch them) and if you choose this method, it is your responsibility to add/remove tables from the filters element when they are added/removed from the solution.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •