Dynamics Community Forum Thread Details

Export to Datalake. Power BI refreshes in service, periodically fail.

Our situation:

Microsoft Dynamics F&O Setup with Export to (Synapse) Datalake. i.e. it's constantly updating the files in the Datalake
Synapse Serverless DB, with views that hit those CSV files in the datalake

Refreshes with either Power BI desktop, or in the Power BI service, will intermittently fail. I think the most relevant bits of the error message are:

'Unexpected end-of-input within record... ' <--- This part is always the same

Tables/General/Miscellaneous/PMIPBillingScheduleTable/PMIPBILLINGSCHEDULETABLE_00001.csv'. <--- This part will change to a different table each time.

I think the issue is the files being constantly updated by Microsoft Dynamics FO, because:

Last at night updates: 100% go through fine
Evening updates: 90% go through
Middle of day: 70% go through - i.e. when those CSV files are getting the most updates
Every failure is the same error, but tends to reference a different high-activity CSV file

I've posted over in the Power BI forums, but not gotten any info.

But given this is a MS ERP, going into an MS cloud produce, then read by MS's premier reporting product -surely there must be best practices?

OK, this is not specific to Power BI - was able to recreate the error in SSMS.

Also found some documentation from Microsoft

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/finance-data-azure-data-lake

Q: When I read data files by using Synapse serverless, I notice an intermittent error: "Unexpected end-of-input within record at...."

A: When tables records are frequently updated or deleted in finance and operations apps, you might notice a read/write contention on CSV files, and this error might occur. This issue occurs when the Synapse serverless SQL service queries a CSV file as it's being updated. In many cases, you might be able to fix this issue by retrying the query.

So this isn't a specific to Power BI error.

Quick Links