How can I write to a Dataverse lookup using an alternate key?

Question

Hi

I want to write data to Dataverse via a Synapse Copy Data task in a pipeline. The destination tables all have alternate keys set up.

So for example, I could have a Country entity, with CountryAlternateKey (e.g. ISO code) and Name, and a City with CityAlternateKey, Name and CountryAlternateKey (Lookup to Country).

When I want to write data into the City entity using a copydata task, I can't write the ISO code value to the Country lookup field, as it expects a GUID. Other methods such as Power BI dataflows allow you to use the alternate key fine though, so is there any way to do this in a Copy Data task?

Answer 1

In Azure Synapse or ADF, directly using an alternate key in Dataverse lookups during a Copy Data task can indeed be challenging since lookups require GUIDs, and there isn't native support for alternate keys in these activities.

However you can use a Data Flow activity within your pipeline to perform a lookup on the Country entity before writing to the City entity. This way, you can join the City data with the Country data based on the alternate key (ISO code) and retrieve the necessary GUID. The transformed data with GUIDs can then be written to Dataverse.

You can also store your data temporarily in a staging table (such as in Azure SQL Database). Then, use a Script activity with a T-SQL query or stored procedure to join your City data with the Country entity on the alternate key to fetch the GUIDs. Finally, write the transformed data to Dataverse.

If the above options are too complex, consider using an Azure Function or Power Automate flow to map alternate keys to GUIDs before pushing the data to Dataverse. This approach may involve some custom coding but can be automated within your Synapse pipeline.

Answer 2

Hi @Duncan Pryde

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

Unfortunately, within Azure Synapse or Azure Data Factory, there is no direct support for using alternate keys in Dataverse lookups during a Copy Data activity. Copy Data activities are designed to move data between various sources and destinations, but they don't inherently support complex lookups or transformations based on alternate keys like Power BI dataflows do.

Following your suggestion, utilizing the Dataverse API for direct data entry could be the most adaptable method.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.