x

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.

 cls;
  
   #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 at 04:13 PM in Default

avatar image

thayle
0

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x62
x57
x35
x8

asked: May 22 at 04:13 PM

Seen: 52 times

Last Updated: May 22 at 04:25 PM

Copyright 2016 Redgate Software. Privacy Policy