Updating Primary Keys

[JeffInFlorida]

When a change is encountered for a primary key and the Apply Policy is set to defensive then you can end up with duplicate rows in the target database.
To be able to update primary keys on a target database you have to delete the existing primary key and then insert the new primary key. In order to get the old primary key you need to have the Before Image of the change.
In order to get the before images perform the following steps:
1) Run the Support.bat file that is in the CDC directory of the solution.
2) Export the machine configuration using the following command:
NAV_UTIL EXPORT MACHINE MACHINE.XML
3) Edit the MACHINE.XML file and add the getBeforeImage attribute to the CDC_AGENT adapter config.
<adapter name='CDC_AGENT' type='sqlCDCA' definition='CDC_AGENT'>
<config datasource='BULK' logLevel='none' checkTimeoutEveryNEvents='100'
realTime='true' retryInterval='2' lgrAutoConfig='true' getBeforeImage='true'>
4) Import the configuration .
NAV_UTIL IMPORT SYS MACHINE.XML
5) Stop and start the CDC solution using the Management Console.

Make the following changes to CDC Package for each table.
1) Add a Transformation Script component between the Change Source and Change Destination components.
2) Connect the component to the Change Source.
3) Edit the script component and make the following changes:
a. On the Input Columns check the operation column and change the Usage Type to ‘ReadWrite’.
b. Edit the script and add the following code in the ‘Input0_ProcessInputRow’ function.
if (Row.operation.Trim() == "BEFOREIMAGE")
Row.operation = "DELETE";
if (Row.operation.Trim() == "UPDATE")
Row.operation = "INSERT";
4) Save the scipt.
5) Connect the Script Component to the Destination Component.