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

user_11663555 0
Viewed 830

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?

informatica-powercenter informatica
3 Answers

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

user_116635550

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.

user_64305230

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.

user_28340650
Related