I am sometimes asked how to automate actions on the report server. The answer is to use the rs.exe scripting utility. The utility gives you a host to run VB based script files easily against the Report Server SOAP API. The utility in SQL Server 2005, supports both the 2005 and 2000 endpoints. These are accessed through a command line parameter. The default is the 2005 management SOAP endpoint.
Probably the most common task I’m asked about is how to publish a report using a script. Here’s a sample. You’ll note that I’m reading the RDL from a file. First add yourself to the lukaszpblog group on http://groups.msn.com/lukaszpblog. Then click this link:
The second thing folks ask me about is how to copy the contents of a report server to another server. The easiest way to do this is to copy the data over using a script. Beware however, that certain things cannot be preserved by following this method. These include: 1) Execution and history snapshots, 2) Subscriptions, 3) Properties on items such as created by.
The reasons for each of these are as follows. For Execution and history snapshots the Reporting Services SOAP API does not provide methods for retrieving and publishing snapshots. For Subscriptions, the SOAP API does not let you specify the owner of a subscription. If you copy the subscription and recreate it on the server, it will be owned by the account the script executed under. For Properties such as created by, similarly to subscriptions, RS does not provide APIs for manipulating these properties.
A further word of caution – RS enforces security on all items. As such you need to run your scripting execution with sufficient privilege to see and access all the desired properties of the items you want to move. This may not be trivial and will require pre-planning and post-verification to ensure the process actually copied all the content you wanted.
Take care, and good luck.