Loading records into Dynamics 365 through ADF

Question

I'm using the Dynamics connector in Azure Data Factory.

TLDR

Does this connector support loading child records which need a parent record key passed in? For example if I want to create a contact and attach it to a parent account, I upsert a record with a null contactid, a valid parentcustomerid GUID and set parentcustomeridtype to 1 (or 2) but I get an error.

Long Story

I'm successfully connecting to Dynamics 365 and extracting data (for example, the lead table) into a SQL Server table

To test that I can transfer data the other way, I am simply loading the data back from the lead table into the lead entity in Dynamics.

I'm getting this error:

Failure happened on 'Sink' side. ErrorCode=DynamicsMissingTargetForMultiTargetLookupField,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot find the target column for multi-target lookup field: 'ownerid'.

As a test I removed ownerid from the list of source columns it loads OK.

This is obviously a foreign key value.

It raises two questions for me:

1. Specifically with regards to the error message: If I knew which lookup it needed to use, how can I specify which lookup table it should validate against? There's no settings in the ADF connector to allow me to do this.

2. This is obviously a foreign key value. If I only had the name (or business key) for this row, how can I easily lookup the foreign key value?

How is this normally done through other API's, i.e. the web API?

Is there an XRMToolbox addin that would help clarify?

I've also read some posts that imply that you can send pre-connected data in an XML document so perhaps that would help also.

EDIT 1

I realised that the lead.ownertypeid field in my source dataset is NULL (that's what was exported). It's also NULL if I browse it in various Xrmtoolbox tools. I tried hard coding it to systemuser (which is what it actually is in the owner table against the actual owner record) but I still get the same error.

I also notice there's a record with the same PK value in systemuser table

So the same record is in two tables, but how do I tell the dynamics connector which one to use? and why does it even care?

EDIT 2

I was getting a similar message for msauto_testdrive for customerid.

I excluded all records with customerid=null, and got the same error.

EDIT 2

This link appears to indicate that I need to set customeridtype to 1 (Account) or 2 (Contact). I did so, but still got the same error.

Also I believe I have the same issue as this guy.

Maybe the ADF connector suffers from the same problem.

Answer 1

At the time of writing, @Arun Vinoth was 100% correct. However shortly afterwards there was a documentation update (in response to a GitHub I raised) that explained how to do it.

I'll document how I did it here.

To populate a contact with against a parent account, you need the parent accounts GUID. Then you prepare a dataset like this:

SELECT 
-- a NULL contactid means this is a new record
CAST(NULL as uniqueidentifier) as contactid,
-- the GUID of the parent account
CAST('A7070AE2-D7A6-EA11-A812-000D3A79983B' as uniqueidentifier) parentcustomerid,
-- customer id is an account
'account' [parentcustomerid@EntityReference],
'Joe' as firstname,
'Bloggs' lastname,

Now you can apply the normal automapping approach in ADF.

Now you can select from this dataset and load into contact. You can apply the usual automapping approach, this is: create datasets without schemas. Perform a copy activity without mapping columns