Forum Discussion

bpsearle's avatar
bpsearle
Icon for Resolver II rankResolver II
9 years ago
Solved

An error occured while attempting to publish. The underlying connection was closed. A connection...

I have created a pbix in the desktop. It has data sources from 2 spreadsheets and an on-premises SQL database. When I click publish I receive the following error message: An error occurred while at...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi bpsearle,

     

    According to your description, when you publish the report from desktop to Power BI Service, the error throws out. While you are able to use Get Data-> Files to upload .PBIX file in service, right?

     

    In your scenario, please try to update the Power BI Desktop to the latest one, then publish it again. Also enable the trace log to see detail error messages.

     

     

     

    Besides, as you connect to Excel file and SQL Server database in the report, to set the dataset to use data gateway, you need to configure two data sources: File and SQL Server, which use the same connection information as in Power BI Desktop.

     

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi bpsearle,

 

According to your description, when you publish the report from desktop to Power BI Service, the error throws out. While you are able to use Get Data-> Files to upload .PBIX file in service, right?

 

In your scenario, please try to update the Power BI Desktop to the latest one, then publish it again. Also enable the trace log to see detail error messages.

 

 

 

Besides, as you connect to Excel file and SQL Server database in the report, to set the dataset to use data gateway, you need to configure two data sources: File and SQL Server, which use the same connection information as in Power BI Desktop.

 

 

 

Best Regards,
Qiuyun Yu

  • bpsearle's avatar
    bpsearle
    Icon for Resolver II rankResolver II
    9 years ago

    Hi Qiuyun

     

    Thank you very much for your reply.

    Your first question is correct.

    I'm on the latest version.

    I haven't seen the trace option and will have a look at this.

    In terms of setting up the data source on the portal it is more than likely the problem.

     

    For the data source, I would like to use One Drive For Business for the location of the spreadsheets. How would you configure the data source on the portal, would that still be exactly the same path as on the desktop - just wondering how it resolves a local path to One Drive. For example in the desktop I select the the spreadsheet and the path is c:\users\<my user name>\SharePoint\<project name folder in One Drive for business>. I must admit I haven't tried it and will do so, just thought I'd ask to see what you think.

     

    Thanks very much

    Brian

     

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Icon for Community Support rankCommunity Support
      9 years ago

      Hi bpsearle,

       

      1. In terms of setting up the data source on the portal it is more than likely the problem.

       

      Please follow the suggestion in my original post then test again to use SQL Server and local Excel data sources in one report.

       

      2. For the data source, I would like to use One Drive For Business for the location of the spreadsheets. How would you configure the data source on the portal, would that still be exactly the same path as on the desktop - just wondering how it resolves a local path to One Drive. For example in the desktop I select the the spreadsheet and the path is c:\users\<my user name>\SharePoint\<project name folder in One Drive for business>. I must admit I haven't tried it and will do so, just thought I'd ask to see what you think.

       

      In Power BI Desktop, to connect to the Excel file exists in OneDrive for Business, we can use web data source. Please refer to this article: Use OneDrive for Business links in Power BI Desktop.

       

      By the way, if you connect to Excel use web data source and publish to service, there will have a refresh issue. And product team already realized this issue and are working on it to find the fix.

       

      Best Regards,
      Qiuyun Yu

      • bpsearle's avatar
        bpsearle
        Icon for Resolver II rankResolver II
        9 years ago

        Hi Qiuyun

        Thank you again and for following up on my further questions.
        The link to "Use OneDrive for Business links in Power BI Desktop" provides most of the answer. The bit I'm stuck with is probably the issue you refer to that MS are working on. The problem seems to be that with the "web" data source there is no option for "Organizational Account" and therefore there is no way to configure the data source.
        Do you know if what I describe above is the issue MS are working on? Or if it's not how I configure the data source? The reason I ask is there seems to be very little information about it, or any that I can find.
        On a separate and related point, have you done anything with AD data source? This seems to be similar to OneDrive. It works on the desktop but there is no matching data source on the PBI Service. I have previously assumed that the data sources are the same between the desktop and the service, but now I’m not so sure.
        Many thanks
        Brian