Update Strategy Transformation in Informatica

Update strategy transformation is an active transformation.This transformation is used to insert, update, delete and reject operations using a single mapping.
When you want to maintain a history or source in the target table, then for every change in the source record you want to insert a new record in the target table. When you want an exact copy of source data to be maintained in the target table, then if the source data changes you have to update the corresponding records in the target.
Configuring Update strategy
Update Strategy Expression: We have flag rows by assigning the constant numeric values using update strategy.
We have to flag each row for updating, inserting, deleting and rejecting.
DD_INSERT: Value is ‘0’.  Using the row as Insert.
DD_UPDATE: Value is ‘1’. Using he row as Update.
DD_DELETE: Value is ‘2’. Using row as Delete.
DD_REJECT: Value is ‘3’. Using row as Reject.

  • Update strategy transformation is mostly used with lookup transformation. The row from the source qualifier is compared with row from lookup transformation to determine whether it is already exists or a new record. 
  • If you place an update strategy before an aggregator transformation, the way the aggregator transformation performs aggregate calculations depends on the flagging of the row.
  • Update strategy works only when we have a primary key on the target table. If there is no primary key available on the target table, then you have to specify a primary key in the target definition in the mapping for update strategy transformation to work. 

Implementing Update Strategy Transformation
  • Drag source and target from repository to mapping designer
  • Go to transformation->Create-> Select Update Strategy->OK
  • Copy all ports from source qualifier to update strategy
  • Edit Update Strategy-> Prpoerties->Expression Editor 
  • Write expression like below. 
  • Copy all ports from Update Strategy to Target

Powered by Blogger.