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: 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],  
'[email protected]' 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

When I changed the source SQL to this, it worked:

SELECT 
'ADFTest1' as accountnumber, 'ADF Test 1' as [description],  
'[email protected]' as emailaddress1,
CAST('TST' AS NVARCHAR(50)) as xyz_srcsystem,
CAST('1' AS NVARCHAR(50)) as xyz_srccode

Note: the difference is I did not include the true primary key in the source dataset.

Not that if you want to UPSERT a new record (INSERT) and this isn't based on an alternate key, you have to include a NULL primary key