Workflow Basics Guide

10.5.6
- 10.5
- 10.4.0

Back Next

Using Session-Level Target Properties with Source Properties

You can set session-level target properties to specify how the Integration Service inserts, updates, and deletes rows. However, you can also set session-level properties for sources.

At the source level, you can specify whether the Integration Service inserts, updates, or deletes source rows or whether it treats rows as data driven. If you treat source rows as data driven, you must use an Update Strategy transformation to indicate how the Integration Service handles rows.

This section explains how the Integration Service writes data based on the source and target row properties. PowerCenter uses the source and target row options to provide an extra check on the session-level properties. In addition, when you use both the source and target row options, you can control inserts, updates, and deletes for the entire session or, if you use an Update Strategy transformation, based on the data.

When you set the row-handling property for a source, you can treat source rows as inserts, deletes, updates, or data driven according to the following guidelines:

Inserts.
If you treat source rows as inserts, select Insert for the target option. When you enable the Insert target row option, the Integration Service ignores the other target row options and treats all rows as inserts. If you disable the Insert target row option, the Integration Service rejects all rows.

Deletes.
If you treat source rows as deletes, select Delete for the target option. When you enable the Delete target option, the Integration Service ignores the other target-level row options and treats all rows as deletes. If you disable the Delete target option, the Integration Service rejects all rows.

Updates.
If you treat source rows as updates, the behavior of the Integration Service depends on the target options you select.

The following table describes how the Integration Service loads the target when you configure the session to treat source rows as updates:

Target Option	Integration Service Behavior
Insert	If enabled, the Integration Service uses the target update option (Update as Update, Update as Insert, or Update else Insert) to update rows. If disabled, the Integration Service rejects all rows when you select Update as Insert or Update else Insert as the target-level update option.
Update as Update	Integration Service updates all rows as updates.
Update as Insert	Integration Service updates all rows as inserts. You must also select the Insert target option.
Update else Insert	Integration Service updates existing rows and inserts other rows as if marked for insert. You must also select the Insert target option.
Enable array upsert or update	Integration Service updates or upserts data in batches of arrays. Array update and upsert operations reduce the network traffic and optimizes the session performance. Applicable for Oracle targets.
Delete	Integration Service ignores this setting and uses the selected target update option.

The Integration Service rejects all rows if you do not select one of the target update options.

Data Driven.
If you treat source rows as data driven, you use an Update Strategy transformation to specify how the Integration Service handles rows. However, the behavior of the Integration Service also depends on the target options you select.

The following table describes how the Integration Service loads the target when you configure the session to treat source rows as data driven:

Target Option	Integration Service Behavior
Insert	If enabled, the Integration Service inserts all rows flagged for insert. Enabled by default. If disabled, the Integration Service rejects the following rows: Rows flagged for insert Rows flagged for update if you enable Update as Insert or Update else Insert
Update as Update	Integration Service updates all rows flagged for update. Enabled by default.
Update as Insert	Integration Service inserts all rows flagged for update. Disabled by default.
Update else Insert	Integration Service updates rows flagged for update and inserts remaining rows as if marked for insert.
Delete	If enabled, the Integration Service deletes all rows flagged for delete. If disabled, the Integration Service rejects all rows flagged for delete.

The Integration Service rejects rows flagged for update if you do not select one of the target update options.

Rename Saved Search

Table of Contents

Workflow Basics Guide

Workflow Basics Guide

Using Session-Level Target Properties with Source Properties

Using Session-Level Target Properties with Source Properties