Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mmace1
Impactful Individual
Impactful Individual

Intermittent error refreshing in service, from Synapse Serverless

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 )  

1 ACCEPTED SOLUTION
mmace1
Impactful Individual
Impactful Individual

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

View solution in original post

4 REPLIES 4
mmace1
Impactful Individual
Impactful Individual

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

GilbertQ
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

mmace1
Impactful Individual
Impactful Individual

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. 

Hi, have you solved this issue? I experience the same. 

We also thought...think actually it is related to the csv files being accessed by the synchronisation from D365. 
However, I'm able to SELECT * from the views that cause problems in the refresh within the PBI service. 
The SELECT does finish in minutes without a single problem. 
Refresh in service brings this error message. 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!