Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a Power BI Desktop file published in the Power BI Service that uses 3 different data sources, however these all point to lists in the same SharePoint 2013 (on-premise) site. I have found that by using different data connectors, I can achieve everything I need to for this report. Details of connectors used, are:
I have successfully run this report on a scheduled refresh (twice daily) for the last 6 months via a personal data gateway. We are now setting up an on-premise data gateway and while I have managed to establish two of the three sources successfully (listed above 1 & 2), I am seeing a "File Not Found" error for the third (listed above 3). Here are the details of the error I am seeing:
Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."
Activity ID: | <Activity ID> |
Request ID: | <Request ID> |
Cluster URI: | |
Status code: | 400 |
Error Code: | DMTS_PublishDatasourceToClusterErrorCode |
Time: | Mon Jan 13 2020 11:19:35 GMT+1300 (New Zealand Daylight Time) |
Service version: | 13.0.11747.315 |
Client version: | 1912.2.031 |
GGLAZAUEPBIVM02-Gateway: | Unable to connect to the data source undefined. |
Underlying error code: | -2147467259 |
Underlying error message: | Web.Contents failed to get contents from 'http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll' (404): File Not Found |
DM_ErrorDetailNameCode_UnderlyingHResult: | -2147467259 |
Microsoft.Data.Mashup.ValueError.DataSourceKind: | Web |
Microsoft.Data.Mashup.ValueError.DataSourcePath: | http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll |
Microsoft.Data.Mashup.ValueError.Reason: | DataSource.Error |
Microsoft.Data.Mashup.ValueError.Url: | http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll |
Is there any way we can get this data source successfully added to the new gateway? Please let me know if any of the blanked information above is required. Many thanks in advance 🙂
Solved! Go to Solution.
Hi @Anonymous ,
Try to add the following query into query editor:
let
Source = Web.Contents("http://<ServerName>.com/<SitePath>/<SiteName>",[RelativePath = "/_vti_bin/owssvr.dll?XMLDATA=1&List={<ListID>}&View={<ViewID>}&RowLimit=0&RootFolder=%2f<SitePath>%2f<SiteName>%2fLists%2f<ListName>Selection={<ItemID>}-{<ItemID>}EditWebPage=Formatting=NonePreFormattedTextToColumns=TrueConsecutiveDelimitersAsOne=TrueSingleBlockTextImport=FalseDisableDateRecognition=FalseDisableRedirections=FalseSharePointApplication=http://<ServerName>.com/<SitePath>/<SiteName>/_vti_binSharePointListView={<ViewID>}SharePointListName={<ListID>}RootFolder=/<SitePath>/<SiteName>/Lists/<ListName>&IncludeVersions=TRUE"])
in
Source
if it's okay,then publish the .pbix file to service,and then configure the data source to see whether there's an error.
Best Regards,
Kelly
I have a Power BI Desktop file published in the Power BI Service that uses 3 different data sources, however these all point to lists in the same SharePoint 2013 (on-premise) site. I have found that by using different data connectors, I can achieve everything I need to for this report. Details of connectors used, are:
I have successfully run this report on a scheduled refresh (twice daily) for the last 6 months via a personal data gateway. We are now setting up an on-premise data gateway and while I have managed to establish two of the three sources successfully (listed above 1 & 2), I am seeing a "File Not Found" error for the third (listed above 3). Here are the details of the error I am seeing:
Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."
Activity ID: | <Activity ID> |
Request ID: | <Request ID> |
Cluster URI: | |
Status code: | 400 |
Error Code: | DMTS_PublishDatasourceToClusterErrorCode |
Time: | Mon Jan 13 2020 11:19:35 GMT+1300 (New Zealand Daylight Time) |
Service version: | 13.0.11747.315 |
Client version: | 1912.2.031 |
GGLAZAUEPBIVM02-Gateway: | Unable to connect to the data source undefined. |
Underlying error code: | -2147467259 |
Underlying error message: | Web.Contents failed to get contents from 'http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll' (404): File Not Found |
DM_ErrorDetailNameCode_UnderlyingHResult: | -2147467259 |
Microsoft.Data.Mashup.ValueError.DataSourceKind: | Web |
Microsoft.Data.Mashup.ValueError.DataSourcePath: | http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll |
Microsoft.Data.Mashup.ValueError.Reason: | DataSource.Error |
Microsoft.Data.Mashup.ValueError.Url: | http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll |
Is there any way we can get this data source successfully added to the new gateway? Many thanks in advance 🙂
Hi @Anonymous ,
The 400 ERROR means a bad http request. Please updata on-premise gateway to the lastest version. If you already have updated the gateway to the latest version, then you can refer to steps below for troubleshooting.
1. Check if the server is using a proxy and the proxy. Some proxies restrict traffic to only ports 80 and 443. By default, communication to Azure Service Bus will occur on ports other than 443.
2. You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. You will need to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. Change the value from AutoDetect to Https. This file is located, by default, at C:\Program Files\On-premises data gateway.
3. Restart the gateway and check if the issue persists.
To stop the service, run this command:
''' net stop PBIEgwService '''
To start the service, run this command:
''' net start PBIEgwService '''
4. You may export the data gateway log by selecting Diagnostics and then selecting the Export logs link near the bottom of the page. Reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#tools-for-troubleshooting.
Hope this would help.
Best Regards,
Kelly
Thanks for your reply, we have the latest version of the software and have completed the checks you suggested:
Many thanks,
Olivia
Hi @Anonymous ,
If so ,pls try to shift to HTTPS instead of direct TCP,then restart the gateway and see if the error still occurs.
Best Regards,
Kelly
I have checked and HTTPS was already enabled. Is there anything else we can do?
Thanks
Olivia
Hi @Anonymous ,
Try to use an empty .pbix file to connect with the following URL using a web connector(shown as below):
http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll
Don't write any parameters inside,then publish to service to check whether it can configure the datasource from sharepoint.
Sometimes it may happen when the gateway machine cant visit the datasource, using the above steps to rule out the factor.
Best Regards,
Kelly
I tried this and I get file not found errors in both Power BI Desktop and in a browser. When I enter the full URL I see the data in both places. I think this is due to the conversion of the data to XML? That's what the rest of the URL defines.
My colleague configuring the gateway has also confirmed that when he puts the full URL into the browser on the server the gateway is configured on, he sees the XML data as well.
Thanks
Olivia
Hi @Anonymous ,
Try to add the following query into query editor:
let
Source = Web.Contents("http://<ServerName>.com/<SitePath>/<SiteName>",[RelativePath = "/_vti_bin/owssvr.dll?XMLDATA=1&List={<ListID>}&View={<ViewID>}&RowLimit=0&RootFolder=%2f<SitePath>%2f<SiteName>%2fLists%2f<ListName>Selection={<ItemID>}-{<ItemID>}EditWebPage=Formatting=NonePreFormattedTextToColumns=TrueConsecutiveDelimitersAsOne=TrueSingleBlockTextImport=FalseDisableDateRecognition=FalseDisableRedirections=FalseSharePointApplication=http://<ServerName>.com/<SitePath>/<SiteName>/_vti_binSharePointListView={<ViewID>}SharePointListName={<ListID>}RootFolder=/<SitePath>/<SiteName>/Lists/<ListName>&IncludeVersions=TRUE"])
in
Source
if it's okay,then publish the .pbix file to service,and then configure the data source to see whether there's an error.
Best Regards,
Kelly
May I also get help for my web page data source? I have posted something similar on this page:
Seeing that this post has been resolved by modifying the URL of the web source, it's possible that this can apply to my case as well ☹️
This has resolved the issue, this data source can now connect to the gateway successfully. Thanks very much for your help 🙂
Olivia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.