cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mfco
Level: Powered On

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

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:

 

 

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?

 

screenshot_Data_Integration.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mfco
Level: Powered On

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

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.

 

CDSDataIntegrationKey1.png

 

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.

1 REPLY 1
mfco
Level: Powered On

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

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.

 

CDSDataIntegrationKey1.png

 

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.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 198 members 4,481 guests
Please welcome our newest community members: