Azure Data Factory copy data to CDS - cannot convert 'System.String' to target type 'Microsoft.Xrm.Sdk.EntityReference'

Question

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

Answer 1

Hi @KevinStone-8034,

Welcome to Microsoft Q&A and thanks for your query.

Were you mapping entity reference column to a virtual column with a special naming pattern {lookup_field_name}@EntityReference ?

Could you please share a screenshot of your mapping for the entity reference column that you are having issue with?

I would also suggest you to please refer to this doc: Writing data to lookup field using ADF copy

---

Thank you

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

@KevinStone-8034 when you say "All I had to do was add @EntityReference to the destination column name it it worked no problem." how did you do this specifically? I'm not seeing that option to update the Destination it only allows me to pick from the target fields.