PowerExchange Interfaces for PowerCenter

10.5.2
- 10.5.8
- 10.5.6
- 10.5.5
- 10.5.4
- 10.5.3
- 10.5.1
- 10.5
- 10.4.1
- 10.4.0 HotFix 1
- 10.4.0

Back Next

Treating Source Rows as Updates

PowerExchange extraction maps include the DTL_CAPXACTION column, which indicates whether the change is an insert, update, or delete. When you import an extraction map in Designer, PWXPC includes this column in the source definition. The PowerCenter Integration Service uses the column to determine which action PowerCenter applies to the row when the row reaches the target.

You might need to configure a session to treat each source row as an update, regardless of the value of the DTL_CAPXACTION column. For example, suppose the change stream includes multiple row inserts that have the same value for a non-key field in the source table. If this field is the key field of the target table, an attempt to insert the rows into the target table results in a primary key constraint violation.

To configure a session to move each source row to the target as an update, complete the following steps:

In the Task Developer, double-click the session to edit it.

Click the

Properties

tab.

For the

Treat Source Rows As

attribute, select

Data driven

In the Mapping Designer, add an Update Strategy transformation to the mapping.

Open the Update Strategy transformation.

Click the

Properties

tab.

Click the button next to the

Update Strategy Expression

field.

Enter an update strategy expression to always mark rows as updates.

Validate the expression and click

Click

Connect the ports in the Update Strategy transformation to another transformation or a target instance.

When you run the session, PowerCenter moves each source row to the target as an update if possible. If a row with a matching primary key value is not present in the target system, PowerCenter inserts the row.

For CDC data sources, if you select

Insert

Update

, or

Delete

for the

Treat Source Rows As

property, the Integration Service ignores the selection and uses the action that is indicated in the DTL_CAPXACTION column.

Rename Saved Search

Table of Contents

PowerExchange Interfaces for PowerCenter

PowerExchange Interfaces for PowerCenter

Treating Source Rows as Updates

Treating Source Rows as Updates