How to Resolve Missing or Null Columns When Copying Data from Dataverse to Azure SQL Using Dataverse Connector?

Question

I am currently working on a project where I need to copy data from a Dataverse table to an Azure SQL database using the Dataverse connector in Azure Data Factory (ADF). However, I am encountering the following issues:

1. Some columns from the Dataverse table are being skipped and are not copied to the Azure SQL table.
2. Some columns in the destination table are populated with null values, even though they contain valid data in Dataverse.

To address this, I am considering using the OData connector instead of the Dataverse connector. My questions are as follows:

1. Will using the OData connector help resolve the issues of missing or null columns when copying data from Dataverse?
2. If yes, could you please guide me on how to create a linked service for the OData connector and configure it to retrieve data from Dataverse?

Any insights or step-by-step guidance to resolve these issues would be greatly appreciated.

Answer 1

Hi @Akshay Patel
Welcome to Microsoft Q&A platform and thanks for posting your query here.

I can assist you with your questions regarding copying data from Dataverse to Azure SQL using the OData connector in Azure Data Factory.

Missing columns

• Columns that are not explicitly enabled for export to dataflows may not appear in the output.
• System or logical columns that are auto-generated by Dataverse may also be skipped.

Solution: Check the Dataverse table settings and ensure all required columns are marked for inclusion. You can do this in the Power Apps interface by reviewing the column metadata.

Null values in destination columns

Null values in the destination could be caused by:

• A mismatch in data types between Dataverse and Azure SQL.
• Columns in Dataverse that contain empty or default values but appear as NULL during transformation.

Solution: Use a Data Flow or add a derived column transformation in Azure Data Factory to handle NULL values and map them to default or appropriate values in the destination table.

could you please guide me on how to create a linked service for the OData connector and configure it to retrieve data from Dataverse?

Sure, here are the general steps involved in creating a linked service for the OData connector and configuring it to retrieve data from Dataverse:

• Create a new linked service in ADF and select the OData connector.
• In the OData connector settings, specify the URL for the Dataverse OData endpoint. The URL should be in the following format: https://.crm.dynamics.com/api/data/v9.0/
• Specify the authentication method for the OData connector. You can use either OAuth or basic authentication, depending on your requirements.
• Test the connection to ensure that the OData connector can successfully connect to the Dataverse OData endpoint.
• Create a new pipeline in ADF and add a copy data activity.
• In the copy data activity, select the OData connector as the source and the Azure SQL connector as the sink.

Comparison: Dataverse Connector vs. OData Connector

1. Dataverse Connector: Easier to use and optimized for Dataverse but may have limitations with certain columns or data types.
2. OData Connector: Offers more control and flexibility through query customization but requires additional configuration.

If your primary goal is to address the missing columns and null values, the OData connector could be a good alternative. However, before switching, I recommend verifying the metadata configuration in Dataverse to ensure all columns are enabled for export.

Reference:

• https://learn.microsoft.com/en-us/azure/data-factory/connector-odata?tabs=data-factory
• https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-odata-dataflows-migration?source=recommendations

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.