cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nitramstr Frequent Visitor
Frequent Visitor

Report Server API pach datasource failing

Hi.

 

We have a server with possibly hundreds of user created reports the need to have their connectionstrings updated.

I've tried to get this to work using the API and a python script but without luck.

 

Link to swaggerhub´s patch method.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/UpdatePowerBIReportDataSource

 

The 'get' method works fine but the 'patch' returns 400 Bad request.

Has anybody been successful with this?

 

Using this RS Version 1.5.7074.36177 (May 2019)

code snippet 

import json
import requests

from requests_negotiate_sspi import HttpNegotiateAuth 

uri = 'http://localhost/Reports/api/v2.0/PowerBiReports(17872a26-8066-4a96-b149-c4f327f91daf)/DataSources' payload = {'ConnectionString': 'data source=GRAL-PROD-DB01,51491;initial catalog=IFD_Journal_AL;persist security info="false"'} headers = {'Content-type': 'application/json'} r = requests.patch(uri, auth=HttpNegotiateAuth(), json=payload, headers=headers) print(r.status_code) r = requests.get(uri, auth=HttpNegotiateAuth(), headers=headers) print(r.status_code) print(r.content)

 

Here´s the response when run in sublime on windows 10.

CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.
400
200
b'{\r\n  "@odata.context":"http://localhost/Reports/api/v2.0/$metadata#DataSources","value":[\r\n    {\r\n      "Id":"1424c60b-0acb-e911-8346-648099cc70c1","Name":null,"Description":null,"Path":null,"Type":"DataSource","Hidden":false,"Size":0,"ModifiedBy":"VGREGION\\\\marst160","ModifiedDate":"2019-08-30T11:40:44.153+02:00","CreatedBy":"VGREGION\\\\marst160","CreatedDate":"2019-08-30T11:39:22.913+02:00","ParentFolderId":null,"IsFavorite":false,"Roles":[\r\n        \r\n      ],"ContentType":null,"Content":"","IsEnabled":true,"ConnectionString":"data source=xxxxxxxxxxxx;initial catalog=xxxxxxxxx;persist security info=False","DataSourceType":null,"IsOriginalConnectionStringExpressionBased":false,"IsConnectionStringOverridden":false,"CredentialRetrieval":"prompt","CredentialsByUser":null,"CredentialsInServer":null,"IsReference":false,"DataSourceSubType":"DataModel","DataModelDataSource":{\r\n        "Type":"DirectQuery","Kind":"SQL","AuthType":"Windows","SupportedAuthTypes":[\r\n          "Integrated","Windows","UsernamePassword"\r\n        ],"Username":"marst160","Secret":"","ModelConnectionName":"de823fb1-b39a-4acd-9046-2e5f818e4794"\r\n      }\r\n    }\r\n  ]\r\n}'

Any help would be appreciated.

 

 

 

3 REPLIES 3
Super User
Super User

Re: Report Server API pach datasource failing

A pbix report can have multiple data sources. I have not tried using this API, but I think at the very least you will need to include the "Id" attribute in your request json so that the server knows which data source you are updating.

 

If you use something like fiddler or the chrome dev tools you can see the body of the request that the PBIRS portal sends when you update a connection string through the UI. You could copy one of those requests and then start seeing if there are properties that the UI sends which you could strip out.

nitramstr Frequent Visitor
Frequent Visitor

Re: Report Server API pach datasource failing

Hi.

 

The report only have one datasource and the Id for it is provided in the uri above.

 

Good point using fiddler.

I tried this but realised I cannot change the connectionstring at all i the GUI.

Both fields "Type" and "Connection string" are greyed out.

 

Is this a setting I can change on the server?

Super User
Super User

Re: Report Server API pach datasource failing


@nitramstr wrote:

The report only have one datasource and the Id for it is provided in the uri above.


So the Id in the uri should be the report Id. The data source Id would then be in the body of the report

 

I tried this but realised I cannot change the connectionstring at all i the GUI.

Both fields "Type" and "Connection string" are greyed out.

 


You definitely can't change the type of the data source. It would break your query if you changed the type. And from what I've seen they only support changing certain types of connections. We primarily have pbix reports in live connect mode and you can change the server/database for those. But I don't know if you can alter data sources for import mode models. These seem to be disabled in the UI, so I suspect the API won't let you change these either

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 405 members 4,383 guests
Please welcome our newest community members: