dynamics-crmazure-data-factorycommon-data-servicexrmtoolbox

Error when using Alternate Key in CDS connector


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

Also see this Doco GitHub I raised:

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


Solution

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

    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
    

    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