Automating SSRS Report Deployment for CI

I am a big fan of automating as much as possible, especially when it comes to deployment and QA. One of the items I recently looked at automating is report deployment for SSRS.

The Standard Approach - Using rs.exe to Deploy Reports using Reporting Service Script(RSS)

SQL Server Reporting Services 2008 includes a command line utility for report deployment called rs.exe. Unfortunately, in order to use this tool, you have to pass in a Reporting Server Script(RSS) file consisting of VBScript. Writing Reporting Server Script(RSS) is not easy as you are writing it without intellisense and the documentation and examples are less than organized or complete.

While trying to find documentation on RSS Script files I discovered a 3rd party utility called RSS Scripter which generates the scripts for you. Unfortunately, this tool has not been updated to support SQL Server 2008 R2 and also works by deploying reports from a reporting server to another reporting server. I wanted a solution that could deploy reports that had not already been deployed and thus needed something that could deploy from the file system to a reporting server.

The Better Approach - Using the SOAP API

While researching how to automate deployment of SSRS reports, I stumbled upon a semi-hidden gem of SSRS. Apparently SSRS has a built in SOAP API published via ASMX web service that you can use for deployment and configuration.

SQL Server Reporting Services actually publishes several endpoints. Two of them, ReportingSerice2005.asmx and ReportingService2006.asmx are now deprecated in SQL Server 2008 R2. The service recommended for use with SQL Server 2008 R2 is located at http:///ReportServer/ReportService2010.asmx

Using these services is the same as using any other WebService. I personally felt this approach was superior to the RSS approach for several reasons

• Better API documentation and examples
• Code can be written in C# using Intellisense with compile time checking rather than being written in VB Script with no intellisense

References

• http://msdn.microsoft.com/en-us/library/reportservice2010.aspx
http://msdn.microsoft.com/en-us/library/ms154699.aspx
• http://msdn.microsoft.com/en-us/library/ms155134.aspx