Adventures with Dataverse: Migrating Microsoft SQL Server Tables to Dataverse Tables

From time to time, I want to migrate data from Microsoft SQL Server to Microsoft Dataverse.

Dataverse offers a lot of capabilities for working with entities within the Power Platform. Sure, I could use virtual tables, but those comes with a lot of limitations.

I also wanted to create a pattern for Microsoft SQL Server to Dataverse migration.

I wanted to keep in the spirt of low code, specifically leveraging the Power Platform where I could, so I decided to use dataflows for the migration.

Dataflows are a self-service, cloud-based, data preparation technology. Dataflows enable customers to ingest, transform, and load data into Microsoft Dataverse environments, Power BI workspaces, or your organization’s Azure Data Lake Storage account. Dataflows are authored by using Power Query, a unified data connectivity and preparation experience already featured in many Microsoft products, including Excel and Power BI. Customers can trigger dataflows to run either on demand or automatically on a schedule; data is always kept up to date.
Microsoft Learn – What are dataflows?

I decided I would try to migrate the AdventureWorks sample database, which can be found at AdventureWorks sample databases – SQL Server | Microsoft Learn.

The sample database includes one-to-many and many-to-many relationships, common to most enterprises.

Here are the steps I used in my migration:

Document all the SQL tables that need to be imported, including primary keys, secondary keys and foreign key relationships.
Create dataflow to create the Dataverse entities and load with the initial data, this will not include the relationships.
- Add column transformations to populate the Name field for Dataverse entities.
- Run the dataflow.
Fix any errors, such as field type mappings, e.g., in my import I had to increase the size of a text field to greater than 100.
Verify additional keys were created in Dataverse entities, sometimes the primary keys are not setup correctly as additional keys.
Add relationships to the Dataverse entities by creating new columns of type lookup pointing to the related tables.
Add choice columns to the Dataverse entities.
Create a dataflow to reload the data with relationships, this will map our lookup columns to the correct Dataverse entities based on the secondary keys.
Add calculation columns, e.g., I added an Amount column to the SalesOrderDetail table that replaces the LineTotal column and calculates the value based on UnitPrice, UnitPriceDiscount and OrderQty.
Add required rules to columns, e.g., required fields, minimum and/or maximum values for numeric data, etc.

Document all SQL tables

The tables I would be importing included:

Table Name	Primary Key Column(s)	Foreign Key Column(s)
Address	AddressID (int)
Customer	CustomerID (int)
CustomerAddress	CustomerID (int) AddressID (int)	CustomerID (Customer) AddressID (Address)
Product	ProductID (int)	ProductCategoryID (int) ProductModelID (int)
ProductCategory	ProductCategoryID (int)	ParentProductCategoryID (int)
ProductDescription	ProductDescriptionID (int)
ProductModel	ProductModelID (int)
ProductModelProductDescription	ProductModelID (int) ProductDescriptionID (int)	ProductModelID (ProductModel) ProductDescriptionID (ProductDescription)
SalesOrderDetail	SalesOrderID (int) SalesOrderDetailID (int)	SalesOrderID (SalesOrderHeader) ProductID (Product)
SalesOrderHeader	SalesOrderID (int)	CustomerID (Customer) ShipToAddressID (Address) BillToAddressID (Address)

Create dataflow to create the Dataverse entities and load with the initial data

Navigate to https://make.powerapps.com.

Click Dataflows, and then click New dataflow.

Set Name to AdventureWorks Data Import (Step 1).

Click Create.

Click SQL Server database.

Enter Connection settings.

Click Next.

Select all the SQL tables to import.

Click Transform data.

Add column transformations

Select the Address table.

Click the Add column tab.

Click Custom column.

Figure 6 – Add the custom column for name

Set Custom control formula to [AddressLine1] & " " & [City] & ", " & [StateProvince] & " " & [PostalCode].

Click OK.

Right click the Custom column, and click Rename.

Rename the column to Name.

Right click on the Name column, click Change type and click Text.

Repeat for the Customer table.

Set Custom control formula to [FirstName] & " " & [LastName] & " (" & [CompanyName] & ")".

Click Next.

Click on each table and do the following:

Set Delete rows that no longer exist in the query output to checked.
Set Alternate key columns to the Primary Key of the original table.

For the tables CustomerAddress, ProductModelProductDescription, SalesOrderDetail set Unique primary name column to Auto-generated.

Ideally, we would like to leave these columns blank as they are not necessary, but we don’t have that option in the import.

For the table ProductDescription set Unique primary name column to Description.

Review all settings, and then click Next.

Run the dataflow

Click Publish.

The new flow will be published and then a refresh will be attempted.

Figure 8 – Dataflow publishing and refreshing

Fix any errors

When our dataflow ran the first time I encountered an error, to see the details for the error click on the (…) next the flow and click Show refresh history.

Click the Start time of the latest run, should only be one.

Review the error message.

In my case, the Description column for the ProductDescription entity needs to support more than 100 characters.

To fix the error click on Tables.

Find the ProductDescription table in the list, should be named SalesLT ProductDescription and select it.

Click Columns under Schema.

Click Description Primary name column.

Click Advanced options.

Set Maximum character count to 500.

Click Save.

Navigate back to the dataflow by clicking Dataflows.

Click the (…) next to the AdventureWorks Data Import (Step 1) dataflow and click Refresh.

This time the dataflow should run successfully.

Verify additional keys were created in Dataverse entities

Sometimes the primary keys are not mapped as additional keys in the Dataverse entities, not sure why this happens.

Click Tables.

Click SalesLT Address table.

Click Keys under Schema.

You should see a Key called PrimaryKey, if you don’t, you will need to add it.

To add a missing key click New key.

Set Display name to PrimaryKey and select AddressID.

Click Save.

Repeat for each of the tables.

Table Name	Primary Key Column(s)
Address	AddressID (int)
Customer	CustomerID (int)
CustomerAddress	CustomerID (int) AddressID (int)
Product	ProductID (int)
ProductCategory	ProductCategoryID (int)
ProductDescription	ProductDescriptionID (int)
ProductModel	ProductModelID (int)
ProductModelProductDescription	ProductModelID (int) ProductDescriptionID (int)
SalesOrderDetail	SalesOrderID (int) SalesOrderDetailID (int)
SalesOrderHeader	SalesOrderID (int)

Add relationship columns

We need to add the following relationships to our tables:

Table Name	Foreign Key Column(s)
CustomerAddress	CustomerID (Customer) AddressID (Address)
Product	ProductCategoryID (int) ProductModelID (int)
ProductCategory	ParentProductCategoryID (int)
ProductModelProductDescription	ProductModelID (ProductModel) ProductDescriptionID (ProductDescription)
SalesOrderDetail	SalesOrderID (SalesOrderHeader) ProductID (Product)
SalesOrderHeader	CustomerID (Customer) ShipToAddressID (Address) BillToAddressID (Address)

Navigate to Tables and select the SalesLT CustomerAddress table.

Click Columns.

Click New column.

Set Name to SalesLT Customer.

Set Data type to Lookup.

Set Related table to SalesLT Customer.

Click Save.

Repeat for Address.

Repeat for all remain relationships.

For the SalesLT ProductCategory table, I named my field for the ParentProductCategoryId to SalesLT ProductCategory.

For the SalesLT ProductModelProductDescription table the relationship for the SalesLT ProductDescription table will generate an error due to the length of the Relationship name.

Set Relationship name to productmodelproductdescription.

Figure 14 – Fix relationship name for SalesLT ProductDescription relationship

For the BillToAddressID and ShipToAddressID fields in the SalesLT SalesOrderHeader table, I named those relationship SalesLT Address (BillTo) and SalesLT Address (ShipTo).

Add choice columns

The status for the sales order in the SQL Database is managed by a User Defined Function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION &#91;dbo].&#91;ufnGetSalesOrderStatusText](@Status tinyint)
RETURNS nvarchar(15)
AS
-- Returns the sales order status text representation for the status value.
BEGIN
    DECLARE @ret nvarchar(15);

    SET @ret =
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;

    RETURN @ret
END;
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status tinyint)

RETURNS nvarchar(15)

-- Returns the sales order status text representation for the status value.

BEGIN

DECLARE @ret nvarchar(15);

SET @ret =

CASE @Status

WHEN 1 THEN 'In process'

WHEN 2 THEN 'Approved'

WHEN 3 THEN 'Backordered'

WHEN 4 THEN 'Rejected'

WHEN 5 THEN 'Shipped'

WHEN 6 THEN 'Cancelled'

ELSE '** Invalid **'

END;

RETURN @ret

END;

Let’s add this a choice.

Click Choices.

Click New choice.

Set Display name to SalesLT SalesOrderHeader Status.

Add the choices, updating the Label and Value fields respectively.

Figure 16 – Set choice properties and options

Click Save.

Click Tables.

Click SalesLT SalesOrderHeader table.

Click Columns under Schema.

Click New column.

Set Display name to Status (Choice).

Set Data type to Choice.

Set Sync this choice with to SalesLT SalesOrderHeader Status.

Figure 17 – Creating choice column in SalesLT SalesOrderHeader table

Click Save.

Create a dataflow to reload the data with relationships and choices

Click Dataflows, and then click New dataflow.

Set Name to AdventureWorks Data Import (Step 2).

Click Create.

Click SQL Server database.

In Connection settings enter Server and Database and then select the connection that was created when we created our first dataflow.

Click Next.

Select on the SQL tables with relationships.

CustomerAddress
Product
ProductCategory
ProductModelProductDescription
SalesOrderDetail
SalesOrderHeader

Click Transform data.

Click Next.

Select SalesLT CustomerAddress from Queries.

Select Load to existing table.

Set Destination table to the SalesLT CustomAddress table, notice the name includes the prefix, e.g., cr110_SalesLTCustomerAddress.

Set Select key (optional) to PrimaryKey.

Click Auto map.

Leave the Source column for Name set to (none).

Set the Destination column for AddressID and CustomerID accordingly.

Repeat for the remaining tables and then review.

For the SalesLT SalesOrderHeader table set the Destination column for StatusChoice to Status.

Click Next.

Click Publish.

After the dataflow runs, review the tables to ensure the relationships were mapped correctly.

Figure 20 – Verifying relationships mapped correctly for SalesLT CustomerAddress

Add calculation columns

I now want to add a calculated column to the SalesLT SalesOrderDetail table.

Click Tables.

Click the SalesLT SalesOrderDetail table.

Click the (+) in the SalesLT SalesOrderDetails columns and data section.

Set Display name to Line Total (calculated).

Set Data type to Formula (preview).

Set Formula to OrderQty * (Decimal(UnitPrice) - Decimal(UnitPriceDiscount)).

Click Save.

Add required rules to columns

If you know some columns are required, you can update those in column properties.

Figure 22 – Making LastName required in SalesLT Customer table

You can also add constraints to numeric fields.

Figure 23 – Making OrderQty required in SalesLIT SalesOrderDetail table and assign a minimum and maximum value

Moving to a different environment

Bonus content!

If you want to move this to a different environment, simply create new solution and then import the dataflows and the Dataverse tables.

I know this seems like a lot of work, and one might want an “easy” button, but I think doing it this way is just a better practice, it forces you to revisit your database structure, get familiar with the data again, address any possible legacy issues in the data, and then import it in such a way that you can accelerate the application building process.

As always, if there is a better way, please share!

Discover more from Matt Ruma

Subscribe to get the latest posts sent to your email.

5 Replies to “Adventures with Dataverse: Migrating Microsoft SQL Server Tables to Dataverse Tables”

Pingback: Adventures with Dataverse: Playing with Security Roles – Matt Ruma
Terica Cuperus says:

January 28, 2024 at 6:02 pm

Hi Great job putting this together! I question what AI will do in this space over the next five years.

Loading...

1. mattruma says:
  
  February 19, 2024 at 7:25 am
  
  Great question! Hopefully it will make it easier. As “fun” as it is to figure out how to do this, it is a lot of trial and error. Certainly AI can help do this better.
  
  Loading...
  
Divakar Kohli says:

March 20, 2024 at 12:23 pm

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

Loading...

1. mattruma says:
  
  March 20, 2024 at 4:53 pm
  
  A few thousand records.
  
  Take a look at https://learn.microsoft.com/en-us/power-platform/admin/capacity-storage for determining storage costs.
  
  Loading...

Adventures with Dataverse: Migrating Microsoft SQL Server Tables to Dataverse Tables

Document all SQL tables