Change DataSource of SSRS Report with Powershell

Starting with the answer from another question like this which works to set all the reports to the same Data Source. I need to see the reports data source reference and set the data Source to the same value as the reference defined in the report. How can I dynamically pick up the report reference and assign it as the data source Item in the forEach-Object loop instead of using the static Data Source parameter value?

This code picked up from the previous answer works to assign a single Data Source to all the reports in the list.

   #Set variables:
   $reportserver = "thayletestvm1:80";
   $url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL";
   $newDataSourcePath = "/Data Sources/CPC";
   $newDataSourceName = "CPC";
   $reportFolderPath = "/Rprl/1000 Transactions/1100 Daily By Toll Day";
   $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential
   $reports = $ssrs.ListChildren($reportFolderPath, $false)
   $reports | ForEach-Object {
   $reportPath = $_.path
   Write-Host "Report: " $reportPath
   $dataSources = $ssrs.GetItemDataSources($reportPath)
   $dataSources | ForEach-Object {
   $proxyNamespace = $_.GetType().Namespace
   $myDataSource = New-Object ("$proxyNamespace.DataSource")
   $myDataSource.Name = $newDataSourceName
   $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
   $myDataSource.Item.Reference = $newDataSourcePath
   $_.item = $myDataSource.Item
   $ssrs.SetItemDataSources($reportPath, $_)
   Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)";
   Write-Host "------------------------"
more ▼

asked May 22, 2017 at 04:13 PM in Default

avatar image


(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 22, 2017 at 04:13 PM

Seen: 111 times

Last Updated: May 22, 2017 at 04:25 PM

Copyright 2018 Redgate Software. Privacy Policy