Update Strategy Transformation Overview

1.       Note: You can also use the Custom transformation to flag rows for insert, delete, update, or reject.

2.       Mapping level use of Update strategy transformation gives more control on the rows

a.       Dd_insert -0

b.      Dd_update-1

c.       Dd_delete-2

d.      Dd_reject-3

·         Any other value will insert

·         We need to mark the session as data driven

3.       Forwarding Rejected rows:

a.       By default, the Integration Service forwards rejected rows to the next transformation

b.      The Integration Service flags the rows for reject and writes them to the session reject file.

c.       If you do not select Forward Rejected Rows, the Integration Service drops rejected rows and writes them to the session log file.

d.       If you enable row error handling, the Integration Service writes the rejected rows and the dropped rows to the row error logs. Doesn’t generate reject file.

e.      If you want to write the dropped rows to the session log in addition to the row

         error logs, you can enable verbose data tracing.

4.       Aggregator and Update Strategy Transformations

a.       put the Aggregator before the Update Strategy transformation – preferred

b.      If Update Strategy is before aggregator

                                                              i.      if you flag a row for delete and then use the row to calculate the sum, the Integration Service subtracts the value in this row.

                                                            ii.      If you flag a row for reject and then use the row to calculate the sum, the Integration Service does not     include the value in this row.

iii.   If you flag a row for insert or update and then use the row to calculate the sum, the Integration Service   adds the value in this row to the sum.

5.       Lookup and Update Strategy Transformations

a.       When you create a mapping with a Lookup transformation that uses a dynamic lookup cache, you must use Update

Strategy transformations to flag the rows for the target tables.

b.      When you configure a session using Update Strategy transformations and a dynamic lookup cache, you must define certain session properties

c.        You must define the Treat Source Rows As option as Data Driven. Specify this option on the Properties tab in the session properties.

d.      You must also define the following update strategy target table options:

·         Select Insert

·         Select Update as Update

·         Do not select Delete

e.      These update strategy target table options ensure that the Integration Service updates rows marked for update and

f.        inserts rows marked for insert.

g.       If you do not choose Data Driven, the Integration Service flags all rows for the database operation you specify in the Treat Source Rows As option and does not use the Update Strategy transformations in the mapping to flag the rows.

The following table displays the options for the Treat Source Rows as setting (Session Level)

Insert	Treat all rows as inserts. If inserting the row violates a primary or foreign key constraint in the database, the Integration Service rejects the row.
Update	Treat all rows as updates. For each row, the Integration Service looks for a matching primary key value in the target table. If it exists, the Integration Service updates the row. The primary key constraint must exist in the target definition
Delete	Treat all rows as deletes. For each row, if the Integration Service finds a corresponding row in the target table (based on the primary key value), the Integration Service deletes it. Note that the primary key constraint must exist in the target definition in the repository.
Data Driven	Integration Service follows instructions coded into Update Strategy and Custom transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject. If the mapping for the session contains an Update Strategy transformation, this field is marked Data Driven by default. If you do not choose Data Driven when a mapping contains an Update Strategy or Custom transformation, the Workflow Manager displays a warning. When you run the session, the Integration Service does not follow instructions in the Update Strategy or Custom transformation in the mapping to determine how to flag rows.