Forum Discussion

mmace1's avatar
mmace1
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

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 )  

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

4 Replies

  • 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's avatar
      mmace1
      Icon for Impactful Individual rankImpactful 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. 

      • jlasner's avatar
        jlasner
        New Member

        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. 

         

  • mmace1's avatar
    mmace1
    Icon for Impactful Individual rankImpactful Individual

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