Error when using Alternate Key in CDS connector

Question

I'm using the Common Data Service for Apps connector in Azure Data Factory to load data into Dynamics 365

I've done this successfully before using the entity key. See this question: https://stackoverflow.com/questions/62054515/loading-records-into-dynamics-365-through-adf

Now I'm trying to use an alternate key to Upsert records into the account entity. (In this case insert)

In Dynamics

I've created two custom attributes fields in account:

Field name        Data Type    Field Type    Max Length
=======================================================
xyz_srcsystem     Single Line  Simple        50
xyz_srccode       Single Line  Simple        50

Then I created a Key on account which contains these fields:

xyz_alternatekeyaccount

In ADF

Then I used a Copy Data activity in ADF to copy data from a SQL view into the account entity, using the CDS connector as a target.

This my source SQL statement:

SELECT 
CAST(NULL as uniqueidentifier) as accountid,
'ADFTest1' as accountnumber, 'ADF Test 1' as [description],  
'nmcdermaid@xyz.com.au' as emailaddress1,
CAST('TST' AS NVARCHAR(50)) as xyz_srcsystem,
CAST('1' AS NVARCHAR(50)) as xyz_srccode

In the target, in the Alternate key name field I entered the alternate key name: xyz_alternatekeyaccount

The error I get when I run the pipeline is

Invalid type for entity id value

Some test to rule out edge cases:

• if I put a dummy alternate key in, I get _Cannot retrieve key information of alternate key 'xyz_alternatekeyaccountx' for entity 'account'_. This implies it is finding the alternate key correctly
• If I remove the alternate key from the connector, it drops back to the other usual set of errors that I see
• When I pull the entity into SQL using the CDM connector, the custom attributes arrive as NVARCHAR(MAX)
• I've tried casting to these data types: NVARCHAR(MAX) NVARCHAR(50) VARCHAR(MAX) VARCHAR(50)
• If I use the normal key (not an alternate key), and get the datatype wrong (anything other than GUID), I'll get the same error

Also see this Doco GitHub I raised:

https://github.com/MicrosoftDocs/azure-docs/issues/59028

Answer 1

For this specific issue, removing the primary key (accountid) from the source dataset resolved the issue:

 SELECT 
 'ADFTest1' as accountnumber, 'ADF Test 1' as [description],  
 'nmcdermaid@xyz.com.au' as emailaddress1,
 CAST('TST' AS NVARCHAR(50)) as xyz_srcsystem,
 CAST('1' AS NVARCHAR(50)) as xyz_srccode

This dataset inserts and updates based on the alternate key