powerappsdataverse

How can I migrate two tables and their relationship from SQL Server to Dataverse


I am migrating a SQL Server based system to Power Apps, and for this I need to move the data from the SQL Server database to Dataverse. I can't use any third-party integrators (like Kingwaysoft), I have available Microsoft Office (Excel, Access) and a Power Apps premium licence. I have been able to move the data from SQL Server to Access using Access import tool, and from there to Dataverse using an Excel template. The elusive part has been to recreate the relationship between tables.


Solution

  • I was able to migrate the tables and their relationship (one to many) following these steps:

    1. Manually created the tables in the Dataverse with the same columns as the tables I wanted to import. I also added the relationship between them (a look up column).
    2. I created an empty Access database. I opened it and used the Import from SQL Server option, to take the two tables from SQL Server.
    3. In Power Apps, for each of the tables I generated a view and exported to Excel.
    4. I exported the data from Access to Excel for each of the tables.
    5. For each table, I opened the file with the exported data and copied and pasted it into the Excel template file created in the step 3.
    6. For each table, from Power Apps, I open the target table and chose the Import from Excel option, to import the data.
    7. To create the relationship, I created a new cloud flow in Power Automate. 8. In The flow, I get the list of records from the look up table, then iterate the list and for each record I take the primary key and assign it to a variable. I also take the GUID for the record (assigned by the Dataverse automatically when record was added in the table upload) and keep it in another variable.
    8. I use the variable from step 8, and search in the other table (not the look up table) for the foreign key. For that record, I update the look up column (that defines the link or relationship to the look up table) with the second variable obtained in step 8. I save the record.
    9. Once all records have been updated, data is ready and available for use. A check can be done comparing data from source database and data in the Dataverse.