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)
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
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:
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(50)
VARCHAR(MAX)
VARCHAR(50)
Also see this Doco GitHub I raised:
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