Editing SSRS Shared Data Sources with PowerShell 3.0

I have recently had a need to edit the server in the connection string of data sources for several dozen SSRS 2008 R2 reports. Easy I know but this was to be a part of a failover scenario. That means I might not be the one doing it.

So the requirements I had, were:

1.       Automate as much as possible to prevent human error.

2.       Make it easy enough for a trained monkey.

Thankfully there were only a few data sources but due to the requirements just documenting the manual process with pretty pictures wasn’t an option.

I want this scripted in PowerShell.

I want to touch as little as possible with the script. (Should it bugger something up.)

I want it to be simple.

I don’t want to have to answer questions for the trained monkeys.

Originally I was going to run a query against the SSRS servers ReportServer DB with invoke-sqlcmd, edit the output and update the line back in the same way. I ran the query in SSMS to make sure I was getting the correct fields and results.

The results were not what I expected and needed to be converted before it was in plain English. Unfortunately I am not knowledgeable enough in SQL to convert it back, even after some lengthy Google searches. Due to this limitation of my knowledge I started looking at using the SSRS Reportserver service.

When connecting to an SSRS Service through a URL it MUST have “.asmx?wsdl” at the end. Also you should know if your SSRS server is running in “native”, “integrated” or “SharePoint” mode. This tells you which of the following service classes the address needs to include.

ReportService2010

http://msdn.microsoft.com/en-us/library/reportservice2010%28v=sql.105%29.aspx

ReportService2006

http://msdn.microsoft.com/en-us/library/reportservice2006%28v=sql.105%29.aspx

ReportExecution2005

http://msdn.microsoft.com/en-us/library/reportexecution2005%28v=sql.105%29.aspx

ReportService2005

http://msdn.microsoft.com/en-us/library/reportservice2005%28v=sql.105%29.aspx

ReportServiceAuthentication

http://msdn.microsoft.com/en-us/library/reportserviceauthentication%28v=sql.105%29.aspx

So the following code will connect to your MSSQL SSRS 2008 R2 server, look for all shared data sources in the specified “folder”. If the connection string matches the $OldSource variable value it is replaced with the $NewSource variable value and updated. I did many on-line searches so I wouldn’t have to reinvent the wheel but all of them were long, complex and difficult to follow. I think the method I came up with is much easier.