CODEWITHSUNDEEP
powershelldatasourcereportingservices-2005
eghetto
eghetto

Reputation: 263

Change DataSource of SSRS Report with Powershell

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!

Upvotes: 10

Views: 27103

Answers (3)

IvanJ
IvanJ

Reputation: 49

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!

Upvotes: 4

eghetto
eghetto

Reputation: 263

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

Upvotes: 15

e82.eric
e82.eric

Reputation: 313

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

Upvotes: 11

Related Questions