Intermittent error refreshing in service, from Synapse Serverless

[mmace1]

I have a PBIX file that connects to Synapse Serverless.  Specifically, to Views that point to CSV files. The files are being contantly updated by Dynamics FO. 

• It always refreshes fine from Desktop. 
• When Published to the PBI Service, it fails ~ 50% of the time.  I can sit and hit refresh over & over - something like 50% of the time it'll work. No clear pattern. 

The failure error message is always the same as below, just with a different table referenced.

I bolded the parts that stand out to me: 

• The consistet error message (first bolded)
• The ever changing datalake path (second bolded)
  
  Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Microsoft SQL: Error encountered while parsing data: 'Unexpected end-of-input within record at [byte: 7272362]. '. Underlying data description: file 'Tables/General/Miscellaneous/PMIPBillingScheduleTable/PMIPBILLINGSCHEDULETABLE_00001.csv'.\r\nStatement ID: {E49C2C4C-FDC8-4E8B-BD60-7ADF41198990} | Query hash: 0x5DB5B39FB3B72B73 | Distributed request ID: {816D796F-5A04-4623-BD08-1F7C2EF6F60D}. Total size of data scanned is 5468 megabytes, total size of data moved is 92 megabytes, total size of data written is 0 megabytes."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"16"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"synw-dataplatform-prod-001-ondemand.sql.azuresynapse.net;datalakeserverless"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Error encountered while parsing data: 'Unexpected end-of-input within record at [byte: 7272362]. '. Underlying data description: file 'Tables/General/Miscellaneous/PMIPBillingScheduleTable/PMIPBILLINGSCHEDULETABLE_00001.csv'.\r\nStatement ID: {E49C2C4C-FDC8-4E8B-BD60-7ADF41198990} | Query hash: 0x5DB5B39FB3B72B73 | Distributed request ID: {816D796F-5A04-4623-BD08-1F7C2EF6F60D}. Total size of data scanned is 5468 megabytes, total size of data moved is 92 megabytes, total size of data written is 0 megabytes."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"19734"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Project.
  Cluster URI: WABI-US-NORTH-CENTRAL-H-PRIMARY-redirect.analysis.windows.net
  Activity ID: 545479b9-d104-4f04-bb74-2e95375aab06
  Request ID: 645aec4d-f3a4-b65c-505b-be08a3b40f2c
  Time: 2023-09-27 20:16:35Z

(I am running a several month old version of the Data Gateway, and just started the beaucratic process to update that, but I doubt it's a Gateway version issue )  

[mmace1 · Accepted solution]

I've been able to reproduce this using SSMS - so update, it's not a specific to Power Bi issue

[GilbertQ]

Hi @mmace1 

To me this is happening because you are trying to read from a CSV file which is being written to at the same time.

I would suggest only querying data that is say 1 hour or 2 hours old so that those CSV files are not being written to

[mmace1]

That was actually my thinking too - I have a timer set to see if failures are less frequent tonight, when (presumably) the files are updating less. 

There's no way to limit to 'few hours old' data - these are hot files.  Unless we undertook some data engineering effort to complicate our setup. 

It is weird though - SQL Querying is fine, PBI Desktop is fine, its only in the service that the issue is coming up.