Update Strategy – Session Settings in Informatica

  • Updated
  • Posted in Programming
  • 3 mins read


This submit is continuation to my earlier one on update strategy. Here we’ll see the totally different settings that we are able to configure for replace technique at session degree.

Single Operation of All Rows:

We can specify a single operation for all of the rows utilizing the “Treat Sources Rows As” setting in the session properties tab. The totally different values you’ll be able to specify for this selection are:

  • Insert: The integration service treats all of the rows for insert operation. If inserting a brand new row violates the first key or overseas key constraint in the database, then the combination service rejects the row.
  • Delete: The integration service treats all of the rows for delete operation and deletes the corresponding row in the goal desk. You should outline a main key constraint in the goal definition.
  • Update: The integration service treats all of the rows for replace operation and updates the rows in the goal desk that matches the first key worth. You should outline a main key in the goal definition.
  • Data Driven: An replace technique transformation have to be used in the mapping. The integration service both inserts or updates or deletes a row in the goal desk based mostly on the logic coded in the replace technique transformation. If you don’t specify the information pushed possibility when you’re utilizing a replace technique in the mapping, then the workflow supervisor shows a warning. The integration service doesn’t observe the directions in the replace technique transformation.

Update Strategy Operations for every Target Table:

You may specify the replace technique choices for every goal desk individually. Specify the replace technique choices for every goal in the Transformations view on the Mapping tab of the session:

  • Insert: Check this selection to insert a row in the goal desk.
  • Delete: Check this selection to delete a row in the goal desk.
  • Truncate Table: test this selection to truncate the goal desk earlier than loading the information.
  • Update as Update: Update the row in the goal desk.
  • Update as Insert: Insert the row which is flagged as replace.
  • Update else Insert: If the row exists in the goal desk, then replace the row. Otherwise, insert the row.

The under desk illustrates how the information in goal desk is inserted or up to date or deleted for numerous mixtures of “Row Flagging” and “Settings of Individual Target Table”.

Row Flagging Type Target Table Settings Result
Insert Insert is specified Source row is inserted into the goal.
Insert Insert possibility isn’t specified Source row isn’t inserted into the goal
Delete Delete possibility is specified If the row exists in goal, then will probably be deleted.
Delete Delete possibility isn’t specified Even if the row exists in goal, then it won’t be deleted from the goal.
Update Update as Update If the row exists in goal, then will probably be up to date.
Update Insert is specified
Update as Insert is specified
Even if the row is flagged as udpate, it won’t be up to date in Target. Instead, the row will probably be inserted into the goal.
Update Insert isn’t specified
Update as Insert is Specified.
Neither replace nor insertion of row occurs
Update Insert is specified
Update else Insert is specified
If the row exists in goal, then will probably be up to date. Otherwise will probably be inserted.
Update Insert isn’t specified
Update else Insert is Specified
If the row exists in goal, then will probably be up to date. Row won’t be inserted in case if it not exists in goal.

Leave a Reply