I am trying to set a lookup field in the copy data action in Azure Data Factory from SQL to the Common Data Service (CDS) for Apps.
The error I'm getting running the pipeline is
Failure happened on 'Sink' side. ErrorCode=UserErrorTypeConversionFail,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The
attribute 'ks_supervisor' with value 'E3BEFA28-C7A6-EA11-A812-000D3A1BB8EF' cannot be converted from
original type 'System.String' to target type 'Microsoft.Xrm.Sdk.EntityReference'.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,''Type=Sy
stem.InvalidCastException,Message=Specified cast is not
valid.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,''Type=Microsoft.DataTransfer.Common.Sh
ared.HybridDeliveryException,Message=The attribute 'ks_supervisor' with value 'E3BEFA28-C7A6-EA11-A812-
000D3A1BB8EF' cannot be converted from original type 'System.String' to target type
'Microsoft.Xrm.Sdk.EntityReference'.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,''Type=Sy
stem.InvalidCastException,Message=Specified cast is not
valid.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,
The key piece of information I believe is
value 'E3BEFA28-C7A6-EA11-A812-000D3A1BB8EF' cannot be converted from
original type 'System.String' to target type 'Microsoft.Xrm.Sdk.EntityReference'.
My question is how do I set a lookup field value in D365/CDS/CRM if it is expecting a value of type EntityReference?
I have tried the CDS connector, Dynamics 365, and the CRM connector, but all result in an identical error. I have also looked for ways to try converting the source value from a string to a GUID, but I can't find a way (I tried going to a CSV first to no avail) and I don't know if that will help since it is a GUID not EntityReference either. The source column is from a SQL table of type "uniqueidentifier", but it is projecting automatically to a string for some reason.
Link to same question on Stack Overflow: azure-data-factory-copy-data-cannot-convert-system-string-to-target-type-mi