Change DataSource of SSRS Report with Powershell

Question

I'm trying to ahange data sources of multiple SSRS Report with Powershell to one shared data source on my reporting server. Here my code:

cls;  
$reportserver = "myServer";<br/>
$url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL";";<br/>
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService";

[ReportingWebService.DataSource[]] $myDataSource = new-object ReportingWebService.DataSource
$myDataSource[0].Name = "myDS"";<br/>
$myDataSource[0].Item = New-Object ReportingWebService.DataSourceReference<br/>
$myDataSource[0].Item.Reference = "/Data Sources/MyDS"<br/>

$reports = $ssrs.ListChildren('/DH', $false)

$reports | ForEach-Object {<br/>
$reportPath = $_.path<br/>
 Write-Host "Report: " $reportPath<br/>
 $dataSources = $ssrs.GetItemDataSources($reportPath)<br/>
 $dataSources | ForEach-Object {<br/>
              Write-Host "Old source: $($_.Name), $($_.Item.ConnectString)"<br/>
              $ssrs.SetItemDataSources($reportPath, $myDataSource)<br/>
              Write-Host "New source: $($_.Name), $($_.Item.ConnectString)"<br/>
          }<br/>

 Write-Host "------------------------"
}

But I'm getting the following error when calling "SetItemDataSources"-method:

***Argument "1" having the value "ReportingWebService.DataSource[]" of "SetItemDataSources" can not be converted to type "ReportingWebService.DataSource[]".***

The question is: What's wrong? The types are the SAME!

Gusta

Answer 1

Thanks e82.eric!

you led me to the working solution. Here it is:

cls;

#Set variables:
$reportserver = "myServer";
$newDataSourcePath = "/Data Sources/MyDS"
$newDataSourceName = "MyDS";
$reportFolderPath = "/DH"
#------------------------------------------------------------------------

$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 "------------------------" 
            }

Gusta

Can you help me on this #41825116 – Dabney

Answer 2

I was having the same problem.

Its not a great solution but according to http://www.vistax64.com/powershell/273120-bug-when-using-namespace-parameter-new-webserviceproxy.html. The namespace parameter of New-WebServiceProxy is somewhat broken. The post suggests using the auto generated namespace which ended up working for me so I think you could do this.

$reportserver = "myServer"
$url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL"
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

$proxyNamespace = $ssrs.GetType().Namespace

$myDataSource = New-Object ("$proxyNamespace.DataSource") 
$myDataSource[0].Name = "myDS"
$myDataSource[0].Item = New-Object ("$proxyNamespace.DataSourceReference")
$myDataSource[0].Item.Reference = "/Data Sources/MyDS"

$reports = $ssrs.ListChildren('/DH', $false)

$reports | ForEach-Object {
$reportPath = $.path
Write-Host "Report: " $reportPath
$dataSources = $ssrs.GetItemDataSources($reportPath)
$dataSources | ForEach-Object {
Write-Host "Old source: $($.Name), $($.Item.ConnectString)"
$ssrs.SetItemDataSources($reportPath, @($myDataSource))
Write-Host "New source: $($.Name), $($_.Item.ConnectString)"
}

Write-Host "------------------------" }

Wharfinger

good idea! now the error is different: "The data source 'myDS' cannot be found". It is strange - I know that this datasource DOES exist! :( – Gusta

I get error: Property 'Name' cannot be found on this object; make sure it exists and is settable. $myDataSource is Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1NG_ReportService2005_asmx_WSDL.DataSource I think is $myDataSource.Name. – Mirilla

what version of ssrs are you using? Is it in native mode or sharepoint? – Wharfinger

Answer 3

I have packaged up this info in two nice scripts:

http://gallery.technet.microsoft.com/scriptcenter/Get-SSRS-Data-Source-19360302

http://gallery.technet.microsoft.com/scriptcenter/Set-SSRS-Data-Source-3b074747

Enjoy!

Advert

any version without use Sharepoint, only SSRS ? For me fails in "get-spweb". – Mirilla

IvanJ thanks for the scripts. It saved my bacon and worked out great. @Mirilla Make sure that you are running this from a SharePoint Management Shell, or have the SharePoint PowerShell snapin installed. – Inflict

Getting below error for SP2013 "Exception calling "GetItemDataSources" with "1" argument(s): "The permissions granted to user 'NT AUTHORITY\ANONYMOUS LOGON' are insufficient for performing this operation. --->" – Cognoscenti

As far as I can tell, the sharepoint stuff doesn't exist, or can't be installed anymore (WIndows 10) – Waverly