SQL to Dataverse Data Migration using Azure Data Factory | Power Apps

[@mannymorales7913]

Great video, Andrea. Thank you for producing and sharing. Excellent content,including the little troubleshooting snippets. Great work, definitely helpful!

[@correagregorio]

Awesome video, lots of different learning sessions in one! Thank you!

[@maryjaggers78]

Hello from Utah! This is exactly what I needed! Very informative and easy to understand, thank you!

[@amandamoore9183]

Useful for anyone stressing on Guids and I’d mappings to sql dB, very well presented.

[@andreapinillos]

Thanks, Amanda!

[@robbiemckenzie6468]

Loved the demo, subscribed, and waiting for moe.

[@nizarhaddaji918]

Clear as water

[@panja850]

It really helped me in understanding the purpose of data factory, and quick small data migration.

[@JasonPrice-i2f]

We are undertaking a project to migrate a SQL Server database to Dataverse and this is very helpful. We are looking at different ways to migrate the data over. This method looks promising 🙂

[@abhiramk5782]

Great! Very useful and informative video

[@rafaelapinillos6]

Incredibly helpful! Love your informative videos!

[@mahdia3509]

Very helpful! Thanks for such a clear and informative video!

[@necdetsaritas8722]

Great job. Thanks, Andrea

[@bvs271289]

Very informative and helpful video for beginners

[@DSEI-PrashunDash]

Thank you so much for this video. Though, i did not understand everything but now i do have some idea what needs to be done for linking sql db with dataverse. Thank you so very much!!

[@angelini27]

Very helpful and clear!

[@rajarangasamy1831]

Thanks Andrea for this video

[@satishgawade3538]

reallly helpful.... followed all steps but at time of lookup mapping in adf its saying :: error message: 'new_student' entity doesn't contain attribute with Name = 'new_lessonid' and NameMapping = 'Logical' , used same template also please let me know cause of that

[@andreapinillos]

Hi @satish, thanks for watching! You are probably missing that column in your dataset.

[@satishgawade3538]

Hi @@andreapinillos but student table doesn't having new_lessonid column

[@donrodgers1205]

I would like to perform SQL to Dataverse migration (keeping PK/FK relationships intact for all tables/entities) but I do not have access to Azure Data Factory - what would you suggest? (apart from purchasing a license for Azure Data Factory ;)

[@andreapinillos]

You could use a dataflow, make sure you create a key for your pk in the table before mapping the fields in the dataflow.

[@donrodgers1205]

I tried using a Dataflow but kept receiving "error 26 - error locating server/instance specified" on the connection. Apparently many others are having the same issue but I didn't find much on how to correct it (DNS is resolvable, can ping server, SQL Browser service is running, port is available and listening etc). Regardless, your suggestion about creating additional key columns will be money once I get this error sorted, thank you much!

[@andreapinillos]

You could also use a power automate flow to do a data migration

[@donrodgers1205]

@@andreapinillos Apparently we have cloud to ground disabled for our entire organization so this connection will not work no matter what I try. Looks like I will be writing a plug-in to do the import (ughhh). Thanks for your helpful suggestions, much appreciated.

[@johnfromireland7551]

@@donrodgers1205 Alternatively you can use the Virtual tables functionality in Power Platform Dataverse. You recreate those SQL tables in Dataverse. You can, then, use Power Automate with Dataflows, if required, to copy the data from those new dataverse tables into your existig ones.

[@sidefxs]

What are the differences between this and Dataflows. Are there any advantages or disadvantages to either? Would love to hear your thoughts.

[@andreapinillos]

Hi Jesus, thanks for watching! At the time, I did not have access to import data using dataflows in my environment. Azure Data Factory was a great option for me as it allowed me to map the relationships between the tables from SQL to Dataverse.

[@vibhaskashyap8247]

Thanks Andrea for the video, its indeed helpful. Operation in ADF was marked as UPSERT, was it because lookup mapping had to be performed on Student entity in Dataverse. What if there is 1-1 entity mapping between Azure SQL DB and Dataverse should we still have UPSERT operation for Int to guiid mapping ?

[@ianp0812]

Hi Andrea thanks for sharing. By the way how did you setup the linked service for dataverse?

[@RedCloudServices]

Today in Dataverse I think you can create a virtual table directly with the SQL table have you tested this? we are really struggling to run our stored procedure and have the output automatically go to a dataverse table (without human lift and lay)

[@andreapinillos]

Yes, I have worked with virtual tables in Dataverse. When I worked with it, it was taking too long for data to render in my Model-driven app but I am happy to create a video on how to set one up.

[@skipa9906]

Thanks Andrea for the video, can I do the migration of tables from DB2 to Dataverse?

[@andreapinillos]

You’re welcome! Yes, you should be able to choose that database instead of SQL.

[@skipa9906]

@@andreapinillos Thank you i will give it a try

[@DavidHewes-g2t]

How do you delete the current records first using ADF?

[@johnfromireland7551]

Use Alter Row Transformation: In the Data Flow, use the Alter Row transformation to mark all rows for deletion by setting a condition that always evaluates to true (e.g., true()). This Data Flow will then process the deletion of all records in the specified Dataverse table.

[@divakarkohli8108]

Hello Andrea, How much data did you migrate? How did you determine the license cost of usage and cost of storage in Dataverse.

[@johnfromireland7551]

In the video you can see it is 4 rows...

[@Memphans]

Great video! thanks. What if my source data is in excel, how to resolve this lookup GUID issue. I have been looking for a solution for few days now.

[@andreapinillos]

Follow the same process of creating an extra column in dataverse.

[@rameshbabuc5981]

Very helpful! One Query. We are looking for Azure data lake to Dataverse connection. Example if entry created in data lake table those entries reflect in Dataverse. Also we are planning to build App based on the Dataverse data which received from Datalake. So once data added into Dataverse table it should sync with Datalake also. Kindly suggest implementation steps/ reference for above cases.

[@mihirrakhonde9563]

have you got any solution for it yet ?

[@BipinKumar1991]

This is really helpful! How do we map choice column based on string value from SQL to Dataverse? For example Lesson Type field in Lesson table is Choice type and we wanted to map this using string value present in SQL from Azure Data factory...

[@andreapinillos]

Hi Bipin, thanks for watching! I did not have to map any choice fields in my use case so I did not get a chance to explore how to execute this. Let me know if you are able to find a solution!

[@vibhaskashyap8247]

Can you share some insight on Data migration of files like pdf, ppt, docx, csv, xlsx to CRM? Can you suggest some details Data migration for this scenario using ADF?

[@johnfromireland7551]

CoPilot Agents can read those files and extract their data and probably populate a given Dataverse table. I haven't tried it myself. You can also use Power Automate AI Hub Actions for that instead. Or you can use Azure AI Foundry service.

[@tarkeshwarkumar7708]

is it real-time solution?

[@karlnathaniel5900]

Hi sorry to disturb, Im a teenager playing around with microsoft services and I dont know much about where Im supposed to get the service uri and the service principal ID on the dataverse linked service, the "same idea here" kinda made me cry because I was following along.😅

[@andreapinillos]

Hello! Go to my very first video where I explain how to get a free subscription. Then watch the videos following that first one to get a good idea of how to get started.

[@ruthlessluder]

Volume is too low

[@CEMBerthoud]

HOLY S&*#T I've been trying to get Azure SQL tables to work in Power Apps for almost a year! I never realized the primary key would be an issue!

[@andreapinillos]

So glad you were able to find this helpful!