How to truncate Dynamics 365 entities with Data Factory (and copy to Azure data lake)?

Question

I am currently using a Data Factory to copy entities from Dynamics 365 in bulk to an Azure Data Lake. The entities are saved as CSV files in the Data Lake every 24 hours.

Instead of bulk copying, I would like to truncate entities to new data and append to the files that already exist in the data lake.

I think this is a common operation for SQL databases, but can this be done between Dynamics 365 and a Data Lake?

Answer 1

You could add a filter to your queries to get those records that have been modified within the last 24 hours.

Additionally you can setup Dynamics to replicate its data to an external SQL database.

Replicate data to Azure SQL Database

Answer 2

Azure Data Lake storage Gen2 as a source type only support three Copy behaviors.

I tried the three cope behaviors, they all could not help you append to the files that already exist in the data lake. If you choose the exist file, when the copy active completed, the exist file will be overwrite.

Fore more details, you can reference: Azure Data Lake storage Gen2 as a source type.

It can not be done between between Dynamics 365 and a Data Lake with Azure Data Factory.

Thanks for James Wood provided a good solution for us. And Combine my answer and his , the problem will be solved.

Hope this helps.