Tutorial corner

Informatica,ETL,oracle,sql/plsql

update without update strategy in informatica

Spread the love

Update without update strategy in informatica : Last time we went through update strategy transformation in informatica with example. Update Strategy transformation let you insert, update , delete data on target side . It can also reject the incoming data depending upon your condition . But one question arise , can we update without update strategy in informatica ? Lets go through below article , how  can we update records in informatica even without update strategy transformation and what is the difference between these two approaches ?

Check here for how to filter null values in informatica

Update Records by update strategy transformation

We saw one example of update strategy transformation in informatica , where we update/insert/delete records in employee table. While using  update strategy transformation , we update the flag of records for insert/update/delete in update strategy expression in update strategy transformation

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

Update without update strategy in informatica

We can also update records in informatica by changing the properties of “Treat Source rows as” in Properties tab of session task.

update without update strategy in informatica Part-1

update without update strategy in informatica part-1

During session configuration, you can select a single database operation for all rows using the Treat Source Rows as setting from the ‘Properties’ tab of the session.

  • Insert :- Treat all rows as inserts.
  • Delete :- Treat all rows as deletes.
  • Update :- Treat all rows as updates.
  • Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.

Please note that we need to update Treat Source Rows as Data Driven while using update strategy transformation. Otherwise it will ignore flag in update strategy expression and will treat all rows with same operation as mentioned in Treat Source Rows.

When we select Treat Source Rows as update , we can set more option in Target option.

update without update strategy in informatica part-2

update without update strategy in informatica part-2

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

So in case we want to update all records , we can set below option.

Properties tab in session:-

Set Treat Source Row as Update

Target Table option :-

  • Check Insert
  • Uncheck Update as Update
  • Uncheck Update as Insert
  • Check Update else Insert 

So in this article , we went through explanations how to update without update strategy in informatica.

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