Issue loading data to Dynamics using Azure Data Factory

luis nogueira 1 Reputation point
2022-10-10T10:26:07.537+00:00

Hello everyone,

I have the following issue in a copy activity:

"Code": 23605,
"Message": "ErrorCode=DynamicsOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: -2147088238, error message: A record that has the attribute values SAP Client, SAP System Id, OrderPOS-ID, Order ID, SAP Module, Account Assignment ID already exists. The entity key Purchase Order Atl Key requires that this set of attributes contains unique values. Select unique values and try again..,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,'";

  1. I'm using a dynamic query(with a store procedure) and load that based on config table where i have all entities to load. And Recently we start to get this error messages for some countries(but there's no pattern). The entity in this case is Purchase Orders and the processes load part of the data to dynamics, but some countries are failing. I've already checked all possible duplicates and I didn't find none using the keys presented in message above;
  2. This fields are defined as alternatekeys on dynamics in order to avoid duplicates, and the alternatekey are composed with: SAP Client, SAP System Id, OrderPOS-ID, Order ID, SAP Module, Account Assignment ID;
  3. In order to make our process faster I separate PO(PurchaseOrders) in 2 view: (1)Purchase_Order_master : basically copy to dynamics new records with all keys and after this first view run we have the (2)view that got all attributes and do the upsert of all values that suffered some update on source side.
  4. In this 2 view we have the following field in common, these are the only fields in common
    sie_purchaseordersid(uniqueidentifier, null)
    Executionid(uniqueidentifier, not null)
    Hashkey(varbinary(32), not null)
  5. The fields that compose the hashkey are the same that we can find on the alternate key
    ,HASHBYTES('SHA2_256', CONCAT(SAPSYSID,SAPMOD,MANDT,EBELN,EBELP,TRY_CONVERT(NVARCHAR(255),TRY_CONVERT(INT,ZEKKN)))) HashKey

I don't have more ideas, and i hope one of you already tackled a similar issue in a passed and can provide some help.

Thanks in advance

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