Tutorial corner


Update Strategy Transformation in Informatica with Example

Spread the love

Update Strategy Transformation in Informatica , is a connected and active transformation. Till now you have seen that, most of the transformation in Informatica are use to insert the records. Update Strategy transformation let you insert, update , delete data on target side . It can also reject the incoming data depending upon your condition.For example : when a customer is changing address , you can use this update strategy transformation to update data in your Customer Tables. Here we will go through the properties of Update Strategy Transformation. We will also discuss the steps of adding /configuring Update Strategy transformation in Informatica Mapping. We will also check some performance tuning related guidelines for Update Strategy Transformation in Informatica.

Business Scenarios of Update Strategy Transformation:

Update Strategy Transformation is mainly used in Datawarehouse project to update the Target Database with the help of some Delta records (Changed data which need to be updated).

Is Update strategy Transformation Active or Passive ?

Update Strategy Transformation is an Active transformation as it can change the no of rows by rejecting some of them .

Is Update strategy Transformation Connected or Unconnected ?

Update strategy Transformation is a Connected Transformation.

Check here for Normalizer transformation in informatica

Properties of Update Strategy Transformation in Informatica

  • Active /Passive : Update Strategy Transformation is an Active transformation as it can also reject rows.
  • Connected/Unconnected Transformation : Update Strategy Transformation is a connected Transformation.
  • Types of ports in Update Strategy Transformation:
    • Input : For reading input Data
    • Output : For providing output Data

Updating Rows in Informatica

You can define update operation on below two levels:

Update Strategy on Session level

We can instruct Informatica Service to treat all rows in the same way or use same instruction by configuring Target mapping at session level. You can edit the update strategy of Target on session level by following below steps

  • Create a session for the current mapping
  • Now Edit the session by clicking on Session -> edit
  • Go to Properties tab and set the properties of Target as mentioned below

Set below option as per your need.

  • Insert: Select this option to insert all rows into a target table.
  • Delete: Select this option to delete a rows from a table.
  • Update: Under Update set below sub -option
    • Update as Update. Update each row flagged for update if it exists in the target table.
    • Update as Insert. Inset each row flagged for update.
    • Update else Insert. Update the row if it exists. Otherwise, insert it.
  • Truncate table: Select this option to truncate the target table before loading data.

Update Strategy on Mapping level

You can flag a row (for insert , update, delete or Reject depending upon some condition) by adding “Update strategy Expression” in the properties tab of Update Strategy Transformation in a Mapping. Below Flags can be set in Update strategy Expression.

Flag Numeric Value Type Operation
DD_INSERT 0 Insert row
DD_UPDATE 1 Update row
DD_DELETE 2 Delete row
DD_REJECT 3 Reject row

We normally define some condition in update strategy expression to perform any of above action ,

Components of Update Strategy Transformation in Informatica

When you configure a Update Strategy transformation, define the following components:

  • Transformation tab: You can rename the transformation and add a description.
  • Properties tab: You can specify the tracing level, Forward Rejected Rows, and Most Importantly Update Strategy Expression (Type of Action to be taken)
  • Ports tab: Once port under Update Strategy is created , you will see input and output port for Update Strategy.

Configuring Update Strategy Transformation Ports:

You can create input ports by copying them from another transformation or by manually creating them in Ports tab.

Port Details:

  • Port name: Add the name of the port.
  • Datatype, precision, and scale: Configure the datatype and set the precision and scale for each port.
  • Default Value: In case port data is null , you can define its default value..

Configuring Update Strategy Transformation Properties:

Modify the Update Strategy Transformation properties by clicking on the Properties tab.

Property Description
Update Strategy Expressions Type of Operation(Insert, Update, Delete) need to be done. Normally we add IIF , DECODE function to figure out the operation to be done.
Forwarding Rejected Rows You can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them. By default, the Integration Service forwards rejected rows to the next transformation
Tracing Level Amount of detail displayed in the session log for this transformation. The options are Terse, Normal, Verbose Data, and Verbose Initialization.

Check here for Router transformation in informatica

How to use Update Strategy transformation in Informatica:

Use the following procedure to create a Update Strategy transformation in Informatica mapping.

Steps to create an Update Strategy transformation:

  • In the Mapping Designer, open a Mapping.
  • Click Transformation > Create. Select Update Strategy transformation.
  • Enter a name and click Done.
  • You can create ports in Update Strategy by selecting and dragging it from input transformation to Update Strategy transformation.You can manually create input ports on the Update Strategy Ports tab by double clicking on it.
    Update Strategy Transformation step 4

    Update Strategy Transformation step 4

  • Click the Transformation tab and configure transformation properties.

    Update Strategy Transformation step 5

    Update Strategy Transformation step 5


  • Enter an update strategy expression to flag rows as inserts, deletes, updates, or rejects

    Update Strategy Transformation step 6

    Update Strategy Transformation step 6

Note: See that ‘Forward Rejected Rows’ is checked. So that Forward Rejected Rows to Next Transformation/Written to Rejected/Bad File. If not it will write it to session log file.

  • Click OK.
  • Connect output ports of Update Strategy to target

Example of Update Strategy Transformation:

Check here for Update strategy transformation example in informatica

More about Update Strategy Transformation:

Update Strategy Transformation rules and Guidelines:

  • Update strategy need primary key on the target table to work.You should also check if primary key is defined in Target Definition or not.
  • Try to use Update strategy as less as possible because of it slow performance
  • If you are just inserting records , then update strategy transformation should be skipped. You can us direct mapping.
  • To check existing records , you can use Lookup and set the Operation Flag accordingly in Update strategy Expression.

So , in this article we have gone through properties of Update Strategy Transformation in Informatica  ,component/Properties of Update Strategy Transformation , scenarios where to use it , steps to create it and performance related guidelines. Please feel free to give your feedback.

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme