Tuesday, October 12, 2010

RS.exe and rss Scripts for Automation of SSRS 2008 Administrative Tasks

Download .rss script
Sorry everyone, I took down the site that used to host these files. Shoot me an email if you'd like them and I'll send them to you.

Most people that work with SSRS (2005, 2008, 20080 R2) are aware of two ways to deploy Reporting Services catalog items (reports, data sources, shared datasets, images, etc.). These include deploying directly from BIDS (Visual Studio) and by using Report Manager to upload files. There are also a number of web services available that enable us to do this this in a programmatic, automated fashion that allow for things such as dynamic data sources so that we can deploy reports to any environment without having to modify data source properties manually and specifying where in the Report Manager folder hierarchy we want our reports installed - again without having to modify data sources or data sets or references to either.

The following web services are available with for administrative tasks in SQL Server 2008 R2. Note that there is also a ReportExecution2005 web service that provides methods for actually executing reports.
  1. ReportService2005
  2. ReportService2006
  3. ReportService2010
ReportService2005 was used for SSRS instances that are running in native mode.
ReportService2006 was used for SSRS instances that are running in SharePoint integrated mode.
ReportService2010 ties 2005 and 2006 together so that one web service works with both native and SharePoint integrated mode.

While you can work directly with these web services using your programming language of choice, Microsoft was kind enough to create RS.exe. This console application acts as a wrapper for these web services. It is installed with SQL Server and like any other executable it can be called by tools like NANT, MSBuild, InstallShield, etc.

My need for this tool arose when I was developing a set of reports for a commercial product that required installation through InstallShield. The development environment included ANT which we also had call RS.exe for automated deployment in an automated build environment.

Now I'll show you how to use RS.exe from the command line. I won't go into detail about how to use it from specific tools as that should be straightforward for anyone that is familiar with the tool they are using.
Below is a sample RS call with three input parameters.
-i is the file that contains your rss script. More on that later.
-s is the address of your ReportServer web service
-e is the web service end point. Remember from above that there are multiple web services available and you can target any of these endpoints using RS.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -e Mgmt2010


Now that we know how to call RS, the key to making it useful is your custom .rss (Reporting Services Script). It is in these scripts that we can write custom code to deploy reports, data sources, images and create folders as well as delete items.

I have posted a sample rss script that has the following generic methods.
CreateFolder: Creates folders in Report Manager
PublishItem: Publishes reports and shared datasets
PublishImage: Publishes images
DeleteItem: Deletes any item

These methods are essentially wrappers around the rs.CreateCatalogItem, rs.DeleteItem, rs.CreateFolder, rs.SetItemDataSources, and rs.SetItemReferences methods. They add additional exception handling and deal with the nuances of publishing reports in locations different than specified in your Visual Studio project and .rdl files.

Note the use of what are referred to as Global Variables. You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -v rootPath="/Sample"
  -v DWServerName="localhost"
  -v DWDatabaseName="MyDataWarehouse"
  -v sourceFilePath="C:\SourceCode\SSRS"
  -e Mgmt2010

With the sample script you should be able to quickly and easily create an automated process for working with your Reporting Services instances.

Download .rss script

17 comments:

  1. where is the file?

    ReplyDelete
    Replies
    1. If you click one of the two "Download .rss script" links you'll come to a page that contains the file - http://elish.net/Resources.aspx. Look for "Sample .rss Script for RS.exe."

      Delete
    2. Resources.aspx seems to be unavailable. Is this temporary?

      Delete
    3. I'm sorry for the delayed reply. It's temporary but less temporary than I had hoped. If you want to email me at david.elish@informationcollaboration.com I can provide the files to you directly.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hi
    When I choose to end the rs batch with -e Mgmt201
    I have Invalid Endpoint Type
    Do you know what is the problem
    Regards

    ReplyDelete
    Replies
    1. Are you missing a zero on 2010. It should be -e Mgmt2010 not mgmt201.

      Delete
  4. Hi where i will get .rss script?

    ReplyDelete
    Replies
    1. There is a link at the top and bottom of the page (Download .rss script).

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. I have downloaded the .rss file and tried to execute but i am getting the error. Then In modified it and just include to create the folders for Data source and data set but it is still giving error. Please refer to the following link for the error:

    http://182.71.98.12:82/error.png

    This is the rss file that i am using:
    http://182.71.98.12:82/SSRSSampleDeploy.rss

    Please suggest.

    ReplyDelete
  7. Thanks a lot! Great piece information!

    ReplyDelete
  8. Unfortunately writing RSS scripts is not easy. Here is an example of the report deployment tool based on described technology in this article. This command line tool uploads or upgrades the reports one folder at a time. The tool comes with SSRS reports: http://www.rbreporting.com/ssrs_reports.htm

    ReplyDelete
  9. Excellent article .. thank you ..

    -- Rashid Khan

    ReplyDelete
  10. Hi
    this works in SQL Server 2012 ?

    ReplyDelete
  11. Very nice. Thank you for the thorough, well documented example.

    ReplyDelete