Dataflow Gen2 fails on large datasets for BC365 data

[useruserhi91]

I am trying to load data for salesInvoiceLines from BC365 custom/advanced API into a Fabric Warehouse using dataflow gen2.

I notice that I often get this error:
"Error Code: FailedToInsertATable, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Failed to insert a table., InnerException: Dynamics365BusinessCentral: Request failed: The remote server returned an error: (500) Internal Server Error. (Skip token is no longer valid: Sales Invoice Line Aggregate:"

Reducing the size (by filtering on "postingDate" intervals) works, but then I have to make like 15 different runs/partitions of this dataflow with different posting date intervals, which is not viable at all.. I know that I can enable incremental refresh, but I still cannot load the data into the warehouse initially.

How can I fix this in a good way? And preferably not simply by using REST or ODATA in pipelines.

[v-menakakota · Accepted solution]

Hi @useruserhi91 ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

After reviewing the issue of DataflowGen2 fails on large datasets for BC365 data,here are few steps to may resolve the issue:
Instead of manually creating partitions based on "postingDate" intervals, you can automate the process by creating a script or using a tool that dynamically generates these partitions and executes them in sequence. This approach eliminates the need to manage 15 separate runs manually.

Make sure your Dataflow Gen2 is optimized for handling large data loads by:

• Utilizing Azure Data Lake Gen2 storage to stage query results, which can enhance performance.
• Taking advantage of the petabyte-scale copy feature in Fabric Pipelines for quicker data imports.
  
  

If Dataflow Gen2 is still not performing well with large datasets, you can consider Azure Data Factory(ADF) pipelines, as you mentioned not wanting to use REST or ODATA directly. Azure Data Factory allow more control over batching, retry logic, and error handling.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Regards,
Menaka.