question

eghetto avatar image
eghetto asked

Change DataSource of SSRS Report with Powershell

Hi, 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"; $url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL"; $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService"; [ReportingWebService.DataSource[]] $myDataSource = new-object ReportingWebService.DataSource $myDataSource[0].Name = "myDS" $myDataSource[0].Item = New-Object ReportingWebService.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 "------------------------" } 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! Thanks!
ssrs-2008powershellreporting_servicesdatasource
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
I got it now! Here's the working solution: cls; #Set variables: $reportserver = "myServer"; $url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL"; $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 "------------------------" }
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanx! Your a life saver!
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
David Levy wrote a script that [changes the credentials][1] of SSRS reports using PowerShell that I think you might want to compare with yours on how he uses SetItemDataSources. I think your line $myDataSource variable needs to be adjusted in how you are trying to set that up. [1]: http://adventuresinsql.com/2010/11/how-do-i-change-ssrs-report-credentials-with-powershell/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well, thanks. But I know the script by David Levy already. It doesn't help me. I want to change the reference inside the report from development to production SHARED datasource. David Lavy's script just changes credentials of an EMBEDED data source. Anyway, my guess is: "[ReportingWebService.DataSource[]]" is Reporting Service 2000 class, not 2005... Maybe I'm wrong...
0 Likes 0 ·
Debug confirmes that both variables are of the same type but DIFFERENT ASSEMBLIES! "nhdifv-s" vs. "lrc8sdta" [DBG]: PS C:\ > $dataSources.GetType().Assembly.FullName --> nhdifv-s, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null [DBG]: PS C:\ > $myDataSource.GetType().Assembly.FullName --> lrc8sdta, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null [DBG]: PS C:\ > $dataSources.GetType().FullName --> ReportingWebService.DataSource[] [DBG]: PS C:\ > $myDataSource.GetType().FullName --> ReportingWebService.DataSource[]
0 Likes 0 ·
rajinxerox avatar image
rajinxerox answered
Thanks, Worked like a charm. One Small correction in the second one, need to update the $url
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rajinxerox avatar image
rajinxerox answered
Declaring and Defining the $url variable is missing. Other than that, It works perfect
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.