Error while ingesting data into dataverse using Synapse dataflow (Sustainability Manager)

Question

I'm trying to load data into dataverse using Azure Synapse pipelines (Dataflows).

Sink: Dataverse table - Mobile combustion (https://learn.microsoft.com/en-gb/common-data-model/schema/core/industrycommon/sustainability/mobilecombustion)

Below are 4 different combinations I tried for Organizational Unit which is a lookup column. I tried attribute name as msdyn_organizationalunitId & msdyn_organizationalunitid and values as GUID value & name of the organization. None of these combination worked for me.

Can someone help me with what parameter name and value should I pass for a lookup column while inserting a record in Dataverse entity having lookup property. In this case Mobile combustion is the entity name and Organizational unit is the lookup property.

Below is the error message for the last combination:

msdyn_organizationalunitId: 'OrgUnit01'

Microsoft.OData.ODataException: A 'PrimitiveValue' node with non-null value was found when trying to read the value of the property 'msdyn_organizationalunitId'; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.

Answer 1

After spending a couple of months this is what worked for me.

Column name: '[email protected]'
Column Value: '/msdyn_sustainabilityorganizationalunits(<OrgUnitGUIDValue>)'

For any dynamics entity, while writing data to an entity with reference columns, your data set must have reference column names ended with '@odata.bind' and column value must be in '/ReferenceEntity(GUIDValue)' format.