How to copy D365 data into Azure SQL db

Diana Rus 50

Hello,

This is going to be long winded so please bear with me. For the past year I've been extracting data out of D365 using the basic set up of a Synapse Link with incremental folder updates in Power Apps for the dumping of data in a storage account and subsequently using Data Factory with the Microsoft template Copy Dataverse data into Azure SQL using Synapse Link. This has all worked fine, however I am now trying to get the auditing out of Dynamics365, so I've set up a separate Synapse Link and connect to a Synapse Analytics Workspace and uses an Apache Spark Pool for Delta Lake data conversion. This works fine, however I can't use the same template in Data Factory to get the data written into an Azure SQL db. After loads of testing, I've discovered that the model.json file that gets produced in the storage account and that triggers the Data Factory pipeline, is different to the one produced in my regular Synapse Link.

The regular Synapse Link model.json file specifies the partitioning of an entity User's image

The new Synapse Link for auditing does not specify this
User's image

The Data Factory pipeline (specifically the DataFlow that actually does the upsert/delete etc.) relies on partitioning being specified in the model.json file.
User's image

User's image

The screenshots above show that when it comes to processing the data, there is an if condition that will run the actual data flow if there are partitions mentioned in the model.json file. Since this attribute is missing in the Synapse Link for auditing (the entity is partitioned by year), the if returns false and doesn't run the dataflow. If I force it to run the dataflow on false, it runs it successfully, but no data is inserted.

I can't find any information on how to get this data into an Azure SQL db, is there anything in the Microsoft Data Factory template that I need to change? Is there another template or a different solution that needs to be used?

Thank you,

Diana

Chandra Boorla 1,495 Reputation points Microsoft Vendor

2024-08-05T06:50:07.3533333+00:00

Hi @Diana Rus

Thanks for the question and using MS Q&A platform.

As I understand that you are using Synapse Link in Power Apps to export Dataverse data into a storage account with incremental updates. You have two Synapse Links:

Regular Synapse Link: This link extracts data from Dynamics 365 (D365) and stores it in a storage account. The data is then processed by a Data Factory pipeline, which uses a Microsoft template to copy the data into an Azure SQL database. This process works fine.

Auditing Synapse Link: This link extracts auditing data from D365 and stores it in a separate storage account. The data is processed by a Synapse Analytics Workspace using an Apache Spark Pool for Delta Lake data conversion. However, when trying to use the same Data Factory template to copy the data into an Azure SQL database, it fails.

The issue you’re encountering with the Auditing Synapse Link likely stems from differences in how the model.json file is generated and used in the Data Factory pipeline. The "Copy Dataverse data into Azure SQL using Synapse Link" template is primarily designed for regular Dataverse data. Given the specific requirements and differences in the auditing data model, using the same template without modifications might not work seamlessly. However, you can still leverage the template with some adjustments to accommodate auditing data.

Challenges with Auditing Data:

Different Schema: The auditing data might have a different schema compared to regular Dataverse data.

Partitioning Information: The model.json file for auditing data does not include partitioning information, which the default template relies on.

Data Volume and Frequency: Auditing data might have different characteristics in terms of volume and update frequency.

Here are some troubleshooting steps that might help you.

Partitioning Information: The model.json file for the auditing data might not include partitioning information, which is crucial for the Data Factory pipeline to process the data correctly. Since the Data Factory pipeline relies on the partitioning information in the model.json file, you might need to adjust the pipeline to handle cases where this information is missing. You can add a custom activity or script to check for the presence of partitions and handle the data accordingly.

Data Flow Conditions: The Data Factory pipeline’s data flow relies on the presence of partitioning information in the model.json file. If this information is missing, the condition to run the data flow returns false, preventing the data from being processed. Create a custom DataFlow that does not rely on partitioning details from the model.json file. This might involve directly specifying how the data should be processed without relying on the partitioning information.

Update the model.json File: Manually update the model.json file to include the necessary partitioning information. This might involve writing a script or using a tool to add the partitioning details before triggering the Data Factory pipeline.

While the existing "Copy Dataverse data into Azure SQL using Synapse Link" template can serve as a starting point, it may require significant modifications to handle auditing data effectively. By adjusting the DataFlow conditions, schema mappings, and model.json handling, you can adapt the template to work with auditing data. Alternatively, creating a custom pipeline tailored for auditing data might provide a more straightforward and maintainable solution.

If the issue still persists, please do let us know. Glad to help.

I hope this information helps.
Diana Rus 50 Reputation points

2024-08-05T10:43:19.3866667+00:00

Hello @Chandra Boorla ,

Thank you for your answer! I have tried amending the existing dataflow to use single partitioning (since there is no option to disable partitioning alltogether) and this has not worked. Would you be able to offer some suggestions as to how the DataFlow can be modified to get the model.json file processed correctly?

In terms of your other suggestion re. modifying the model.json file, the file from the generic Dataverse copy has attributes related to partitioning all over the file, I wouldn't know where these would need to go in a model.json file that doesn't have them, would you be able to offer any help/suggestions with this?

Thank you

Diana
Chandra Boorla 1,495 Reputation points Microsoft Vendor

2024-08-07T06:09:57.7033333+00:00

Hi @Diana Rus

Apologies for the delay in response.

Please refer the below link that might help you, for loading audit data from Dynamics 365 (D365) into Azure SQL db.

Link: https://www.youtube.com/watch?v=f1DyXNoaTVg&t=558s

I hope this information helps, please do let us know if you have any further queries.
Diana Rus 50 Reputation points

2024-08-07T08:09:18.11+00:00

Hi @Chandra Boorla ,

Thank you for the suggestion, but the video deals specifically with Dynamics Finance and Operations, not CRM. The data export to database is not an option in my use case.
Chandra Boorla 1,495 Reputation points Microsoft Vendor

2024-08-08T16:57:28.9566667+00:00

Hi @Diana Rus

Did you got a chance to refer this document: https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory
Diana Rus 50 Reputation points

2024-08-09T09:41:11.7266667+00:00

Hi @Chandra Boorla ,

This was the the obvious solution and the only one that worked as expected. Thank you for your help!

Could you please help with explaining how log-ins, log-outs work in the audit log? There are access entries for users at weird hours of the day and sometimes createdon and changedata match, sometimes they don't.

Thank you

Diana

Share via

How to copy D365 data into Azure SQL db

Your answer