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