Pages

Thursday, March 14, 2013

Update Strategy - Session Settings in Informatica

This post is continuation to my previous one on update strategy. Here we will see the different settings that we can configure for update strategy at session level.

Single Operation of All Rows:

We can specify a single operation for all the rows using the "Treat Sources Rows As" setting in the session properties tab. The different values you can specify for this option are:

  • Insert: The integration service treats all the rows for insert operation. If inserting a new row violates the primary key or foreign key constraint in the database, then the integration service rejects the row.
  • Delete: The integration service treats all the rows for delete operation and deletes the corresponding row in the target table. You must define a primary key constraint in the target definition.
  • Update: The integration service treats all the rows for update operation and updates the rows in the target table that matches the primary key value. You must define a primary key in the target definition.
  • Data Driven: An update strategy transformation must be used in the mapping. The integration service either inserts or updates or deletes a row in the target table based on the logic coded in the update strategy transformation. If you do not specify the data driven option when you are using a update strategy in the mapping, then the workflow manager displays a warning. The integration service does not follow the instructions in the update strategy transformation.

Update Strategy Operations for each Target Table:

You can also specify the update strategy options for each target table individually. Specify the update strategy options for each target in the Transformations view on the Mapping tab of the session:

  • Insert: Check this option to insert a row in the target table.
  • Delete: Check this option to delete a row in the target table.
  • Truncate Table: check this option to truncate the target table before loading the data.
  • Update as Update: Update the row in the target table.
  • Update as Insert: Insert the row which is flagged as update.
  • Update else Insert: If the row exists in the target table, then update the row. Otherwise, insert the row.

The below table illustrates how the data in target table is inserted or updated or deleted for various combinations of "Row Flagging" and "Settings of Individual Target Table".

Row Flagging TypeTarget Table SettingsResult
InsertInsert is specifiedSource row is inserted into the target.
InsertInsert option is not specifiedSource row is not inserted into the target
DeleteDelete option is specifiedIf the row exists in target, then it will be deleted.
DeleteDelete option is not specifiedEven if the row exists in target, then it will not be deleted from the target.
UpdateUpdate as UpdateIf the row exists in target, then it will be updated.
UpdateInsert is specified
Update as Insert is specified
Even if the row is flagged as udpate, it will not be updated in Target. Instead, the row will be inserted into the target.
UpdateInsert is not specified
Update as Insert is Specified.
Neither update nor insertion of row happens
UpdateInsert is specified
Update else Insert is specified
If the row exists in target, then it will be updated. Otherwise it will be inserted.
UpdateInsert is not specified
Update else Insert is Specified
If the row exists in target, then it will be updated. Row will not be inserted in case if it not exists in target.

No comments:

Post a Comment