It's no small secret that I'm a big fan of using PowerShell to manage SQL Servers. I've gone through all the trouble of loading the SQL Server assemblies and/or configured PowerShell to use the SQL Server provider, and I've jumped into SMO, sometimes to much joy, and sometimes to much sorrow. But, the other day I started to wonder, 'Where's the object model for Reporting Services? And, being a big fan of the Script method from SMO, can I script out an rdl of a report?'
I started digging deeper, and sadly I could find no assembly or aspect of the SQL Server provider that would grant me easy(?) access to the Reporting Services objects. So, I hit the documentation to find out why.
As Books Online was eager to point out, Reporting Services is fundamentally a web service that is accessed via the Simple Object Access Protocol (SOAP) over HTTP. Huh? Well, our friend BOL tries to explain further, stating that the web service has endpoints that you can connect to in order to either kick off reports or manage your report server. So, great, how do I connect to a web service?
The internet abounds with suggestions, most of which revolve around using the Visual Studio and the .NET Framework's SDK in order to create a WSDL in order to create a proxy of the web service, thereby gaining access to the objects and the methods therein. And, as I read those suggestions, I began to empathize, ever so briefly, with developers who must likewise look at relational databases as if there were some sort of horrible, horrible spawn of darkness. Mainly, though, I was wondering how I was going to get around this problem without having to hit up my boss for a license to Visual Studio, and then spending hours to learn the ins and outs of web services, proxies, and using the great and glorious WSDL.exe to create a WSDL file.
But, thankfully, the PowerShell community generally rules, and I was able to find a nifty little PowerShell script by Oisen Grehan which allows me to create a proxy to my Reporting Services without all the aforementioned yuckiness. So, having downloaded the script, scanned it for a basic understanding of what's going on, and then placed it in my trusty C:\PowerShell directory, I fired up PowerShell and ran the following:
$url = "http:///ReportServer/ReportService2005.asmx"
$proxy = C:\PowerShell\New-WebServiceProxy.ps1 -Url $url -SoapProtocol Soap
I provide my credentials at the prompt the script generates, and now, suddenly, I have access to information regarding the properties and methods I'm looking for on my Reporting Services installation with the simple yet powerful:
$proxy | gm
OK, great, so now we can access our Reporting Services objects, but what about generating that rdl for a report? If I know the report name, including it's full deployed path of folders, I can use the GetReportDefinition method to get a byte array of the report:
$report = $proxy.GetReportDefinition("//ReportName")
And, then it's just a matter of converting that byte array to text, and writing it out to a file:
[System.Text.Encoding]::ASCII.GetString($report) >> ReportName.rdl
Still, I think it would be much nice if the SQL Server PowerShell provider made it possible to accessing Reporting Services as part of the SQLServer: PSDrive.