Azure Data Factory - SQL Server to Dataverse

The Power Apps and Automate Architecture Series, presented by Power CAT, will prepare you for upcoming complex Power Platform projects.

This series will provide your architects and developers with the tools and best practices to run successful Power Apps projects of high complexity.

In this session, learn how to configure Azure Data Factory to copy data from SQL Server to Dataverse.

[snodnipper]

Appreciate the video _but_ I continue to be amazed at how awkward the Power App / Dataverse ecosystem is. SQL allows us to define what we want without too much proprietary pain...and endless clicking! Seeing this makes me want to go the other way and decommission as many Dataverse data stores as I can...especially given the query limitations. Anyhow, onwards!

[finlayc]

Thank you. I had been struggling with how to get the GUID to set the lookup.

[mitch]

Question.. I want to pull in data from two separate accounting systems (many tables). Would I have to map all of these tables individually like you did in PowerApps? This seems like a lot. Is there an easier way (Fabric pipeline or something)?

[gunnara.bjarnason]

Hi Andrea. If we hold the source data in Asure SQL, can we use Azure SQL views as a datasource for one CDS table? thus limiting the number of lookup tables in CDS?

[satanklaus]

Thanks! the lookup would also work with optionset? Assuming the source SQL server has labels and not values

[NeumsFor]

ADF team needs to seriously get on creating error flows that write into Dataverse especially in the scenario with Inferred Members encountered (unknown dimension values or code lookup values) during either a fact load or any other part of an ELT/ETL process. Back in 2005, the ProjectREAL team created a nice script component to optimize the failed lookup process. Of course, Master Data Services had not existed yet, but ideally, instead of writing the inferred Member to a dimension, it could have (and should have) been written to MDS.

Thanks for posting.

[RiyazPatanwala]

Hi Andrea. Thanks for posting this video, we are doing exactly something like this to populate a custom entity in F&O from a Azure SQL. We were stuck with the lookup GUID. Can you please post the xml based query for lookup?

Why did you skip the step on how to link CDS? What credentials to use and how to create them...

[zackziss]

Hi there, can you pls share the template used for lookup

[Amr-Ibrahim-AI]

Thanks for the through explanation.
Can the same technique be used with on-premised SQL Server as the source? What should I take care of then?