x

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!
more ▼

asked Feb 07, 2012 at 01:42 PM in Default

eghetto gravatar image

eghetto
2.1k 16 18 24

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

4 answers: sort oldest

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

answered Feb 16, 2012 at 10:31 AM

eghetto gravatar image

eghetto
2.1k 16 18 24

thanx! Your a life saver!
May 16 at 12:56 PM James.Woolfenden
(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered Feb 07, 2012 at 02:32 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

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...
Feb 07, 2012 at 02:38 PM eghetto

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[]
Feb 07, 2012 at 02:52 PM eghetto
(comments are locked)
10|1200 characters needed characters left

Thanks, Worked like a charm.

One Small correction in the second one, need to update the $url
more ▼

answered Jan 09, 2013 at 05:16 PM

rajinxerox gravatar image

rajinxerox
0

(comments are locked)
10|1200 characters needed characters left
Declaring and Defining the $url variable is missing. Other than that, It works perfect
more ▼

answered Jan 09, 2013 at 05:17 PM

rajinxerox gravatar image

rajinxerox
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x113
x28
x24
x7

asked: Feb 07, 2012 at 01:42 PM

Seen: 4199 times

Last Updated: May 16 at 12:56 PM