Data Integration - A record that has the attribute values Id already exists

[Anonymous]

Currently trying to use the Data Integrator (https://admin.powerapps.com/dataintegration) to load data (on a schedule) from and Azure SQL Database to CDS. 

We have successfully set up a Connection Set > Project > Task and can map fields from a SQL Table to the CDS Entity.

However, this only works properly the first time the project is run. 

If we run the project again, it creates duplicate records.

SQL table (omitted additional fields for simplicity):

dbo.Assets

• Id (Primary Key) - int
• Name - nvarchar(128)

CDS Entity

Assets

• AssetId - guid     (default auto generated field for every entity in CDS)
• Id - whole number
• Name - text

AssetId is a unique identifier auto-generated in CDS when a new Asset record is created.

To prevent duplicates, we set the Id (whole number) field in CDS as an Alternate Key. 

However, running the Data Integration project results in the following error: 

• Precondition Failed {"error":{"code":"0x80060892","message":"A record that has the attribute values Id already exists. The entity key Id requires that this set of attributes contains unique values. Select unique values and try again.","innererror":{"message":"A record that has the attribute values Id already exists. The entity key Id requires that this set of attributes contains unique values. Select unique values and try again.

Source Field with issue:

• POST https://_______.crm.dynamics.com/api/data/v9.0/assets {"id":"1", "name: "testName"}

The Data Integrator is unable to determine what is an alternate key. Thus, it tries to run a POST when it should ideally detect an existing alternate key record and run an upsert/PATCH (as specified here: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/update-delete-entiti...).

Does anyone know if there is a way get the Data Integration tool to detect alternate keys in CDS?

From looking at a similar issue (https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/Integration-Job-A-Record-Already-ex...), there is a way to do this via Dynamics, but we are not sure how to do this without Dynamics (straight from Azure SQL).

Thank you

[Anonymous · Accepted solution]

We were able to figure it out.

Had to exit out of the Project, go to the connection set > Integration Key.

https://admin.powerapps.com/dataintegration/connectionsets/YOURCONNECTIONNAME/keys

Note: for the dropdowns to appear here, you may need to REFRESH the browser after adding a new Task. If you need to add an alternate key to CDS this can be done via the entity's web editor under the "Keys" tab.

We then set the Integration/Alternate Key for each entity and running the Project now no longer creates duplicates every sync. It updates the correct records with existing keys. 

MS may want to add this to the official documentation for the Data Integrator as it is currently nowhere to be found.