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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.