In real world ETL we don’t deal with all the rows (DB rows) in one manner. Following explains the need of Informatica Update Strategy Transformation: In ETL some rows will be marked active and you insert them to your relational table, some data rows are meant to be removed from your DB system and some of DB rows are to be updated.
In above situation Informatica Update Strategy Transformation is a transformation that helps you to tag the rows as required before they are processed to Target system. More on Update Strategy Transformation as below ..
What are the different Row flags available in Update Strategy Transformation? Insert, Update, Delete and Reject. Below picture is a small attempt to simplify data row tagging with update strategy transformation.
How to create Update Strategy Transformation (UST)?
- Create UST in Informatica mapping designer
Drag the required ports to UST
Go to Properties Tab -> Create Update Strategy Expression
For example DECODE(ACTIVITY_FLG,
- 1, DD_UPDATE,
The above expression will flag the input rows according to the value of ACTIVITY_FLG
Check “Forward Rejected Rows” if you want to forward rejected rows to next transformation.
Forwarding Rejected rows to next transformation will enable you to capture the rejected records in Informatica Error Handling tables / files. Take a look at the below snapshot
Connect the output ports to the target instance.
How to configure Update Strategy Transformation (UST)?
Choose “Data Driven” in order to use the row flagging done in UST. Alternatively you can configure Update Strategy in Session properties also by choosing the below appropriate options –
- Update as Update – Update flagged rows will be updated in the target.
- Update as Insert – Update flagged rows will be inserted in the target.
- Update else Insert – Row will be inserted into the target if not already exist in the target table.
3 hacks of Update Strategy Transformation-
You have configured Update Strategy transformation in mapping but did not chosen data driven option at Session level. Will you get the desired results as configured in Update Strategy Transformation?
No, Session properties always override the mapping properties. Without “Data driven” set in session, you cannot obtain the desired results with Update Strategy Transformation.
Can you use an aggregator transformation after Update Strategy Transformation?
Yes, but it is suggested to avoid unless it is really required. If not handled carefully, the delete and reject flagged records are dealt in a different manner in Aggregator Transformation leading to the in-accurate results
You have configured Reject file folder and file name in Informatica Session. Can you see rejected records in rejected file?
If the “Forward Rejected Rows” property is turned on, then the reject flagged records will be visible in the reject file.