How to specify an alternate key for mapping to a CDS table using copy activity in ADF

asked 2021-11-11T22:50:59.357+00:00
Nithin Vanam 221 Reputation points

I am trying to read data from a .JSON file in a data lake gen 2 and upsert the data to a CDS table using a copy activity.

In the sink setting of the copy activity i am able to choose the alternate key to perform the upsert.

148579-image.png

And under Mapping, i am mapping my alternate key value from my source to the original field in CDS that is used for the alternate key.

148658-image.png

UNIQUE_KEY is my source key field and esavvy_uniquekey is the field used to create the key ct_componentalternatekey in CDS.

When i debug the copy activity it fails with a error code 2200 and error message :

Failure happened on 'Sink' side. ErrorCode=DynamicsOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: -2147088240, error message: The specified key attributes are not a defined key for the cts_bookingcomponent entity.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,''Type=System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]],Message=The creator of this fault did not specify a Reason.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,'

i am assuming i am missing the mapping of the key field from the source the alternate key in the copy activity. If this is the case, where do i define this within the copy activity? If not any suggestions on what could i be doing wrong here and what do i need to do to get this working? Thanks in advance.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,258 questions
{count} votes

Accepted answer
  1. answered 2021-11-22T22:44:32.7+00:00
    Nithin Vanam 221 Reputation points

    The issue was with the Key i had created in CDS. I didn't realise but the Key creation job in CDS had failed due to that field having duplicate values. Once i deleted the duplicate rows and reactivated the key and published my changes, my copy activity worked in ADF.


1 additional answer

Sort by: Most helpful
  1. answered 2021-11-15T08:40:04.507+00:00
    ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

    Hi @Nithin Vanam ,

    Thank you for posting query here.

    • Could you please check is your linked service pointing to correct CRM instance?
    • Could you check that you're using the Attribute and not the key for your alternate key?

    From error message, it seems issue with key specification. The specified key attributes are not a defined key for the "cts_bookingcomponent" entity. Kindly review this message once?

    Below is documentation link while working with Dynamics connector in Azure data factory.
    https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory

    No comments