Update Shared Datasouce for Report using PowerShell

[JJenkins2005]

Hello,

We are in the process of migrating from SSRS to PBI-RS and have been testing reports in DEV using DEV Datasources.  We have over 300 reports, that we need to switch to Production Datasources, and I've been looking for a way to do it via powershell.. As a NOOB, I'm running into issues.   I downloaded the ReportingServicesTools and it looked like it would be pretty simple... NOPE...   I found that I should be using the Get-RSITEMDatasource and Set-RSITEMDatasource based on  my reading.. so I Ran the following:

PS C:\> $dataSources = Get-RsItemDataSource -ReportServerUri 'http://powerbi.dev.sql.odnss.com/Reportserver/' -RsItem '/docket/All Timekeepers'

Ran Succesfully

Then I ran

PS C:\> Set-RsItemDataSource -DataSource $datasources -RsItem '/AR/AR Aging' -ReportServerUri 'http://powerbi.dev.sql.odnss.com/reportserver/'

I get the error below

Please use Set-RsDataSource to update shared data sources!
At C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.5.0\Functions\CatalogItems\Set-RsItemDataSource.ps1:143 char:17
+ ... throw "Please use Set-RsDataSource to update shared data ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (Please use Set-...d data sources!:String) [], RuntimeException
+ FullyQualifiedErrorId : Please use Set-RsDataSource to update shared data sources!

I'm not trying to update the Shared Datasource itself... I'm simply tying to update the report to use another Shared Datasouce.. 

What am I doing wrong? 

[erico]

I'm stuck at the exact same place. So baffled. Did you ever get this to work? Thanks!

[d_gosbell]

If you want to point to a different shared data source you need to use the Set-RsDataSourceReference cmdlet

[erico]

Hi @d_gosbell

Thank you for the quick response. After reading through the help file for Set-RsDataSourceReference, the examples don't appear to align with my goal. 

I ended up picking apart the function "Set-RsItemDataSource' and grabbing the last section. 

https://imgur.com/Y3B2OE6

This ended up working and set my data source correctly. 

My goal was to set the report data source to this. Does this look sane?

https://imgur.com/pSvkzTI

Thanks! 

Eric

[d_gosbell]

---

@erico wrote:

This ended up working and set my data source correctly. 

---

If you figured out a script that works for you then that's great. If you are getting the result that you desire then that is probably proof in itself. I'm not sure how else you would measure the sanity of this 🙂

[erico]

Hi @d_gosbell,

Maybe "safe" is a better word lol. The section of the function I took is different than your recommendation. My concern is about the downstream effects, despite its immediate success.

Your recommendation sets the DataSource like this... 

$Proxy.SetItemReferences($item, @($dataSourceReference))

I set the DataSource like this... 

$rsProxy.SetItemDataSources($rsItem, $newDataSource)

Thanks,

Eric

[HeyAdmin]

Hey @Per any chance you can post the script in text here?  The link you provided no longer works.  I'm banging my head against the wall.

set-rsdatasourcereference -Path <path to report> -DataSourceName <current report datasource> -DataSourcePath <path to replacement shared data source> isn't working because I can't figure out the correct syntax for providing <current report datasource>.  It's a custom data source.  I used Get-RsItemDataSource which returns a System.Object with two properties:  Name = CM

and

Item = Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy13er_SCCM_ReportService2010_asmx.DataSourceDefinition

I tried providing CM as the <current report datasource> parameter, but no joy.

[d_gosbell]

---

@erico wrote:

Maybe "safe" is a better word lol. The section of the function I took is different than your recommendation. My concern is about the downstream effects, despite its immediate success.

---

Oh in terms of "safety" you should be very safe with calling api methods like this. I sometimes see people in this forum suggesting doing direct updates on the Report Server database and that is definitely unsafe. But the powershell commands go through the supported web api calls which have a bunch of validations and checks in place and should ensure that there are no unwanted downstream effects.