Attunity CDC - Capturing before Image

[SURYA.BHATTACHARYYA]

We recently bought ORACLE CDC for SSIS. I have few questions regarding setup.

1. In case of update, how to set up CDC to capture the before image too?
2. Is there a way to find out only the changed columns? Example: A table has 10 columns, but only 2 of them went through updates. Is this info captured in anywhere?

Thanks

Surya

[JeffInFlorida]

In order to have the CDC solution capture the before image follow these steps:

1) From the management console click on the 'Open local log folder...' link.
2) Move up one directory and run the Support.bat file.
3) Type NAV_UTIL EXPORT MACHINE M.XML
4) Type NOTEPAD M.XML and search for getBeforeImage and change the value to 'true', then save the file.
5) Type NAV_UTIL IMPORT SYS M.XML
6) From the Management Console, stop and start the solution.

You will now get the before images in the staging area that SSIS is reading.

You will have to come up with your own method to compare the BEFOREIMAGE and the UPDATE rows to determine what columns have changed.

NOTE: The SSIS Change Destination component does not process these records and will report them as an error so you must filter those or use your own component to update the target data store.

[DrorHarari]

Capturing the before-image is supported by the underlying technology but was not introduced as a supported use case and hence it is not available in the configuration wizard.

As JeffInFlorida mentioned, when turning on the before-image capture, you must filter out the before-image rows as the SSIS Change Destination component does not know how to handle before-image records.

Still, an important correction to the process suggested by JeffInFlorida is needed - the suggested process is ephemeral (short lived). The moment you re-run the wizard or re-apply the solution configuration, the change would be reversed. Another issue is that the 'getBeforeImage' attribute to be changed would not always exist in the exported file.

The correct way to enable before-image capture (with the noted reservation) is as follows:

1. Before starting, make sure that the solution is stopped, is not open in Visual Studio or in the management console.
2. In the CDC project folder, locate the CDC\AIS\machines folder
3. Under that folder there may be one or more 'machine' folders (depending on the source database type) and under one of them there is a bindings\CDC_SERVICE folder
4. Edit the binding.bnd file (an XML document) in the CDC_SERVICE folder (e.g. with Notepad).
5. Locate a <config> element within an <adapter> element.
6. Add to the config element the attribute getBeforeImage='true' (or change its value to 'true' if it is already there but 'false')
7. Save the binding file
8. Open the management console for the Attunity {some database} CDC for SSIS, locate the relevant solution and switch to the General tab of the CDC Configuration pane. There press Apply Configuration and the solution will be permanently switched to the new mode.

/d

[SURYA.BHATTACHARYYA]

Thanks to both of you. Both works and as was mentioned, the first setting was short lived when the configuration wizard was run again.

I will take care of these before image records.

Thanks
Surya Bhattacharyya