Table loader insert/update causing error .. ERROR: No key variables have been defined

[Roc]

It was working. I need to load a file where some records are new and others are updates. The target table has an index and primary key on claimant_id. I created a table loader with insert/update, Using "Modify using index" on both found and new fields. The lower part usually will gray out the index setting and I set the contrains to both off. Now when I run it says ERROR: No key variables have been defined for file. "XXXNmaeXXX" but one has.   SAS back in 2013 had this : But no fix c'mon load a file like this is a common task. Anyone have any solution?

Problem Note 50965: The Table Loader transformation incorrectly deletes the key column index

The Table Loader transformation incorrectly deletes the index on the key column when the following conditions are true:

1. Load Style is set to Update/Insert on the Load Technique tab in Table Loader properties.
2. Matching Rows is set to either Modify by Column(s) or Modify using Index in the Technique(s) section on the Load Technique tab in Table Loader properties.
3. Before Load is set to Off in the Index Condition section on the Load Technique tab in Table Loader properties.
4. An index exists on the key column used for matching the source table with the target table.

The impact of deletion of the key column index is as follows.

When Matching Rows is set to Modify by Column(s), the update process completes without errors. However, performance can be significantly impacted. The missing index results in the process performs a sequential read instead of an indexed of the target table.

When Matching Rows is set to Modify using Index, the following error will occur due to the missing index:

ERROR: No key variables have been defined for file Target Table.

Contact SAS Technical Support for possible workarounds.

A hot fix is planned for this issue.

Thanks,

[Roc]

It turns out you cannot just add an index or a key in DI. One has to add special code in the code tab and run the job. What is the purpose of haveing something that defines keys or indexs in a table when is dose not work?  Very frustrating this metadata.

[LinusH]

Doesn't it work if you ser index creation to before load - on?