Problem populating a multi target lookup field in D365 (ownerid) from Azue Data Factory

Question

Problem populating a multi target lookup field in D365 (ownerid) from Azue Data Factory

Andrew Macinnes 6

Hi,

I am having problems populating the ownerid field in my account entity in D365 from Azure data factory when I am using a dataflow. The puzzling thing is I am able to populate it when I use a copy activity but when I try to use the same method/mapping from a data flow I get an error.

I have described the issue in more detail in the link below with screenshots to make it easier to see the issue I am having.

https://stackoverflow.com/questions/71753194/adf-copy-activity-and-data-flow-behaving-differently-when-writing-data-to-multi

Any help would be greatly appreciated. Thanks.

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-06-13T11:07:10.933+00:00

Hi @Andrew Macinnes ,
Thankyou for using Microsoft Q&A platform and thanks for posting your query.
As I understand your query , you are trying to copy data from .csv file to D365 datastore using data flow but it's failing, however, same is working in case of copy activity. Please correct me if my understanding is wrong.

Similar issues were faced by customers in past, which got resolved via support ticket after the involvement of product engineering team as well as dynamic engineer . According to that, the owner ID required to assign request as per the policy .

I would encourage you to Create an Azure support request for deeper investigation. Here is the article which can help in walking you through the steps to create support ticket with Microsoft : Create an Azure support request

If you have a support plan you may file a support ticket to get to the root of this , else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID: ____
Subject : Attn Annu

Thanks!
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-06-16T07:03:54.483+00:00

Hi @Andrew Macinnes ,
Just checking in to see if you got a chance to see previous response. Please do revert accordingly.
Reinhard Schwarz 11 Reputation points

2023-03-29T18:06:57.5166667+00:00
I am facing the same problem and am not able to set field ownerid through the Dynamics 365 connector on the Account entity:

Followed the documentation here: https://learn.microsoft.com/en-gb/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory#writing-data-to-a-lookup-field

Specified columns "ownerid" with a GUID value as well as "ownerid@EntityReference" with value "team".

Tried using camel-case SchemaName "OwnerId" and "OwnerId@EntityReference"

Tried automatic mapping at the sink and reverted back to manually map all fields

Found that "OwnerId@EntityReference" is not even available as field in an Inline Dynamics 365 sink, therefore switched back to DataSet.

Tried to revert back to "Insert" operation instead of "Upsert"

Finally also tried modifying the JSON schema assigning "ownerid__team" and "ownerid_team_@EntityReference" to the sink as they do not exist in the schema

The best solution in my eyes would be to simply allow direct assignment of fields in Odata syntax. Why was there even another syntax invented using "@EntityReference" instead of simply forwarding the heavy lifting to WebAPIs in general?

E.g.: "******@odata.bind" with value "teams(123-345-567-789)".

Unfortunately, after hours of wasted time I still didn't find any working configuration, the exception thrown can be found below.

Thrown exception:

Job aborted due to stage failure: Task 1 in stage 19.0 failed 1 times, most recent failure: Lost task 1.0 in stage 19.0 (TID 814, vm-50e81856, executor 1): com.microsoft.dataflow.Issues: DF-REST_001 - Error response from server: Some({"error":{"code":"0x80048d19","message":"Error identified in Payload provided by the user for Entity :'', For more information on this error please follow this help link https://go.microsoft.com/fwlink/?linkid=2195293 ----> InnerException : Microsoft.OData.ODataException: A 'PrimitiveValue' node with non-null value was found when trying to read the value of the property 'ownerid'; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.\r\n at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ValidateExpandedNestedResourceInfoPropertyValue(IJsonReader jsonReader, Nullable`1 isCollection, String propertyName, IEdmTypeReference typeReference)\r\n at Microsoft

Exception thrown at a "Create" operation instead of Upsert:

Unmapped Request found, PathTemplate:~/entityset/key, HttpVerb:POST"}}), Status code: 405. Please check your request url and body. (url:https://myorg.crm4.dynamics.com/api/data/v9.0/accounts(1ddafaf9-e7b4-ec11-983f-6045bd935949),request body: Some({"accountid":"1ddafaf9-e7b4-ec11-983f-6045bd935949","accountnumber":"561674","overriddencreatedon":"2022-04-05T13:55:07Z","overriddencreatedby":"3576ab61-bca3-e911-8162-5065f38be4e1","foc_countryid@EntityReference":"foc_country","ownerid":"22f4abe4-b995-ed11-aad1-000d3ab281b6","ownerid@EntityReference":"team","parentaccountid@EntityReference":"account","primarycontactid@EntityReference":"contact","transactioncurrencyid@EntityR
Reinhard Schwarz 11 Reputation points

2023-03-29T18:38:35.38+00:00
Maybe I can use this opportunity and provide some feedback on Data Factory in general:

Please note, Data Factory seems to be so unstable that I really consider to stop using it for our current and future projects.

Here are some major issues we see on regular basis using data factory designer:

Each step you perform literally takes ages to complete (spin-up, starting debugging, previewing data, ...) and I am just talking about debug-data sources containing 2 accounts.

The UI is often not updating / reflecting the correct schema, fields are missing in dropdowns, modification of the underlying JSON does not always work.

It regularly loses references to flowlets in your data flows, especially when schema changes are necessary.

In such situations Data Factory is no longer able to correctly test connections nor to download and validate schemas of sources and sinks, although when you work in a different data flow (which only makes use of flowlets, unrelated sources, mainly Dynamics 365 connections).

Auto-Mapping is not reliable, schemas are not updated and it also does not return the full schema to subsequent actions

When switching to manual (static) mappings, it keeps on losing them (e.g. if your manual mapping lacks a field and you are forced to do something, NEVER check Auto mapping nor click at Reset!).

Data Factory is sometimes no longer able to render configuration UI elements. E.g., if a sink JSON definition contains a field which was removed from the environment or is otherwise unknown. In such cases, there are no longer any tabs or configuration options available when clicking at your sink.

Overall, using Data Factory is very frustrating!

Just imagine, how happy you are, after you spent half an hour working on one of your mappings and are nearly done, but there's just one or two fields missing in your dropdowns because the UI is not able to keep track on the underlying schema correctly: And in this situation, in the moment of greatest despair, you check the "wrong" box or click at the wrong button and all your work is gone. ;-)

Although my feedback might be unrelated to this topic, I still hope that someone responsible has an interest to improve things and is able to address these issues (product management?).
Pankaj Singh 20 Reputation points

2024-08-22T13:37:39.8466667+00:00

I am facing a similar issue but even the copy activity is failing when trying to populate the multi lookup column 'ownerid' in Account entity.

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-06-13T11:07:10.933+00:00

Hi @Andrew Macinnes ,
Thankyou for using Microsoft Q&A platform and thanks for posting your query.
As I understand your query , you are trying to copy data from .csv file to D365 datastore using data flow but it's failing, however, same is working in case of copy activity. Please correct me if my understanding is wrong.

Similar issues were faced by customers in past, which got resolved via support ticket after the involvement of product engineering team as well as dynamic engineer . According to that, the owner ID required to assign request as per the policy .

I would encourage you to Create an Azure support request for deeper investigation. Here is the article which can help in walking you through the steps to create support ticket with Microsoft : Create an Azure support request

If you have a support plan you may file a support ticket to get to the root of this , else could you please send an email to azcommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Subscription ID: ____
Subject : Attn Annu

Thanks!
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-06-16T07:03:54.483+00:00

Hi @Andrew Macinnes ,
Just checking in to see if you got a chance to see previous response. Please do revert accordingly.
Pankaj Singh 20 Reputation points

2024-08-22T13:37:39.8466667+00:00

I am facing a similar issue but even the copy activity is failing when trying to populate the multi lookup column 'ownerid' in Account entity.

Share via

Problem populating a multi target lookup field in D365 (ownerid) from Azue Data Factory

Your answer