Informatica Power Center - ERROR: "Target table [TABLE_NAME] has no keys specified."

Question

everyone, I've a problem in Informatica PowerCenter.

In my mapping I have 5 objects:

• 1x Source Table
• 1x Source Qualifier
• 1x Expression Transformation
• 1x Update Strategy
• 1x Target Table

The source and target table have no primary key, how come Informatica PowerCenter expects a key?

Answer 1

In Informatica, the ports marked as keys in Target Transformation indicate what should be used to build the Update statement in DB. It has nothing physically to do with real Primary Key defined in the database itself. Usually you use same columns as keys in Informatica and in DB - but this is not necessary. DB is unaware of what is set in Informatica and vice versa.

It's even perfectly valid to have same database table defined multiple times in Informatica and have different mappings that will update the data using different columns as keys.

Note however that if you use Update Strategy you have to define which columns to use as keys.

Answer 2

You have an update strategy in your mapping. Which expects you must have some key defined on target. Infa fires query like

UPDATE tgt SET col =? WHERE KEY = ?

Question mark 1 is updated column and question mark 2 is the key. You can set unique keys as primary key.
If you don't have a primary or unique keys in target, pls define all columns as keys except the updatable column. Or, you can use target overwrite to write sql to update target, but here too, you have to set similar query like above.
Data driven should be set.

Answer 3

I have tried changing the "Treat source rows as" property of my workflow session from "Insert" to "Data driven" and it is working.