Informatica Target Update Override command – Tough Day, i call it being silly :)

Hello All,

Hope you all had a very great Christmas and awaiting to kick off a new year soon. Before we kick off, a happy new year to all of you in advance..!!

Just when i was waking up to welcome a sunny Saturday, i had a thought mugging around me and telling me to do this. Yes to write a post on what i had been badly attacked by, it was indeed not something of a physical attack 🙂

So without further dawdling let me explain how hard i had to fight against the Target Update Override which helps me customize my Update strategy used in Informatica allowing us to Update any target table despite not having the right Primary Key combination for the Update. I have done this before and have done it with a blunder every time which taught me something right but time and again i have done the same repetitively. I just then had thought to help other fellow developers avoid this and so i am writing this blog post to help you out with the Target Update Override usage.

Probably when you go for an Update over your target, you would use the Update Strategy which would link to your target ports, the Primary key columns on which Informatica would perform an update on the rest of the columns you will be linking with the target. So there would also be a scenario where you will wish to update few fields with a conditional check over the other fields (non primary key columns).

There are two ways of doing this,

Method 1 –

Make target structure change on your Informatica side to make the Ports/columns on which you will be performing a check to update the row. This means you will have to change these check fields to have a Primary key property enabled on the Informatica end and then use the Update strategy transformation as a usual way.

Method 2-

To script down a Update sql override in the target. It’s easier to be done with a usual Update query syntax to be written on your target object in the mapping and there is a strict syntax to be followed which also means a very proper spacing to be used between the Operators and the sql literals. By default, the PowerCenter Integration Service updates target tables based on key values. You can override the default UPDATE statement for each target in a target definition. And as discussed we would like to Update on the basis of the Non key columns and this can be done something like below,

Sytax:

UPDATE SALES_TBL SET EMP_NAME = :TU.EMP_NAME DATE_SHIPPED = :TU.DATE_SHIPPED TOTAL_SALES = :TU.TOTAL_SALES WHERE EMP_ID = :TU.EMP_ID

Because the target ports must match the target column names, the update statement includes the keyword:TU to specify the ports in the target transformation. If you modify the UPDATE portion of the statement, use :TU to specify ports.

 I made a mistake..!!

I am just adding this part to save your time. I had done a very silly mistake while composing my Update override query. For a very long time my session was failing and i was unable to figure out what went wrong with the session failing stating i had my Update query wrong.

Revisiting it almost like 100 times changing all that i could and unable to get what went wrong 😦

Finally a big brick hit my head and i just copied my Update sql to the notepad and found that i had no space between WHERE clause and my operand, and i was not able to figure this out as i had it going to a next line character, just as is in below picture.. which i would think was just like a usual word wrap..!!

Hope this helps you and saves your time. Thanks for reading the post and share as it’s fun to learn and share 🙂

Update Override, why? where? when?

A little light on Update Over ride and its use:

We have a an Update strategy transformation to perform any kind of update on the target table, then what is it all abot the usage of Update over ride in a target.

Before we can jump into our topic, for all those who are new to Update startegy -> Update startegy transformation is something that can help you in updating your target table with the new values on the basis of the Primary key match.
Making it simple,”UPDATE tableA set ColumnA = New_Val Where Primary_Key matches”

So now lets take up the use of Update Over ride option, as i was going through this particular scenario i had to Update my Target table with the new values, but not for the Primary Key Column. Rather i was in a position where i had to update the table on the equation of non Primary key column.

As an instance, i had to update the salary of Employees of country Australia. WHere as in my table i do not have the Country column as a Primary key (Employee_ID shall be a Prmary key here). We got to update this!! but how?

Then comes the use of having Update over ride, where i have to lookinto the Properties of my targe table. There we have the “Update Override”. SImple on we have the expression or the Update query to be written as one below,

UPDATE TableA
SET Employee_ID = :TU.Employee_ID, Country = :TU.Country, Salary = :TU.Salary
–Here is where you add condition of check on basis of Non Primary key
WHERE
Country = ‘AUSTRALIA’

And this shall update the records with the new records (changed salary) that have the country = ‘Australia’. SO this is where the Update Override comes as a help in hand.

Thanks for reading, please share. sharing makes us feel good 🙂