Paul Stovell says…

Here at Readify most of our people are on a corporate plan with Vodafone. The company gets a bill at the end of each month, along with a CSV file containing the charges for all of our calls. We can’t send the CSV file out to everyone in the company because they’d be able to see each others calls, so instead we wrote a little parser and some Reporting Services reports to display people’s calls and charges.

To aid in deployment of the reports, I sat down and wrote some Reporting Services Script (.RSS) files. RSS files are written using VB.NET, and executed with the RS.exe tool that ships with Reporting Services. When you invoke the tool, they are loaded, RS.exe injects some local variables, and they are compiled and executed like any other .NET code.

Rather than writing one giant script, I split it out into lots of little helpful scripts which I’ve uploaded in case you find them useful. Here is an overview of each script and their arguments. You can get them from Subversion:

http://subversion.paulstovell.net/Projects/ReportingServicesAutomation/trunk/src/

Or all zipped up:

http://builds.paulstovell.net/ReportingServicesAutomation/trunk/

CreateFolder.rss (view)

Creates a new folder using a specified path. If any of the parent folders don’t exist, they will also be created. Invocation:

rs -i CreateFolder.rss
   -s http://localhost/ReportServer
   -v path="/Path/To/Folder/To/Create"

UploadReport.rss (view)

Uploads a supplied RDL file to a folder on the server. If the report already exists, it will be overwritten. Invocation:

rs -i UploadReport.rss
   -s http://localhost/ReportServer
   -v path="/Path/To/Data/Source/Folder"
   -v reportName="My Report"
   -v reportSourceFile="C:myreport.rdl"

CreateDataSource.rss (view)

Creates a new Data Source on the server, with a given connection string in the specified path. If the data source already exists, it will be overwritten. Invocation:

rs -i CreateDataSource.rss
   -s http://localhost/ReportServer
   -v dataSourceName="MyDataSource"
   -v connectionString="server=(local);database=myDatabase"
   -v path="/Path/To/Data/Source/Folder"

Note that this assumes you are using Integrated Authentication to access your data source, since that’s all we use. You may need to modify the script to suit your needs.

SetDataSource.rss (view)

Maps a given report’s data source reference to a data source that has been created. Invocation:

rs -i SetDataSource.rss
   -s http://localhost/ReportServer
   -v report="/Full/Path/To/My Report"
   -v reportDataSourceName="dsMySource"
   -v dataSource="/Full/Path/To/MyDS"

GrantRole.rss (view)

Changes the policies on an item (folder, report, data source) by placing a given user in the given role. Invocation:

rs -i GrantRole.rss
   -s http://localhost/ReportServer
   -v item="/Path/To/Item"
   -v userOrGroup="Company Management"
   -v role="Browser"

RemoveRole.rss (view)

Changes the policies on an item (folder, report, data source) by removing a given user from a given role (for that item). Invocation:

rs -i RemoveRole.rss
   -s http://localhost/ReportServer
   -v item="/Path/To/Item"
   -v userOrGroup="Company Management"
   -v role="Browser"

RemoveAllRoles.rss (view)

Changes the policies on an item (folder, report, data source) by removing all roles associated with a given user from that item. Invocation:

rs -i RemoveAllRoles.rss
   -s http://localhost/ReportServer
   -v item="/Path/To/Item"
   -v userOrGroup="Company Management"

Reporting Services API

I found the RSS files to be fairly easy to create and run, and the API is very clean and simple. However, in the 8 minutes I spent trying to find one, I couldn’t find an editor for the files that offered intellisense. To find out what API methods were available, I instead had to locate the ReportingService2005 class on Technet.

Deployment Process

For the reporting project I was trying to automate, the deployment process looks like this:

1. We check the report definitions and project into TFS
2. TFS Integrator kicks off the build
3. The Team Build takes the reports and copies them to the drop directory (this is done by modifying the MSBuild file - Team Build won’t build Reporting Services project)
4. TFS Deployer, installed on the test Reporting Services server, invokes a PowerShell script when I change the build quality to “Test”.
5. Inside that Powershell script, we make a number of calls to the above scripts to deploy the reports.

The ironic part about all of this is that when we first deployed the project manually, we lost at least two man-days in coordinating the people needed to deploy it. In contrast, it took about 3.5 hours for me to learn enough about Reporting Services Scripts to write these scripts up and automate it. Here’s to deployment automation!

Filed under: Reporting Services