Process and Tooling

Automated SSRS deployments using RS.exe

SSRS comes with a command line process utility which allows for the deployment of reports to an SSRS server. This has allowed us to easily integrate our report deployments to multiple environments with minimal effort as a part of our standard CI deployment process.

We have structured our deployment reports in the following way:

1.png

Step 1: TeamCity

TeamCity build configurations contain a set of configuration parameters holding values related to the report server. These parameters contain information such as report server URL, paths and directories to where reports will be deployed. The values of these parameters will be injected into the batch file which is called from a TeamCity build step.

2.png

Step 2: Batch File

The batch file (PublishReports.cmd from above) will be used to call the RS process utility.

The general structure of a call to RS will be as follows:

rs -e {ENDPOINT} -i {INPUT FILE} -s {SERVER} -v {GLOBAL VARIABLES}

Firstly, we will define variables required for the running of the script. Secondly, we call the actual RS script, using the values defined in the script as well as those inputted via TeamCity. The script can be seen below:

SET REPORTSERVER= %1
SET RS= %2
SET TIMEOUT=60
%RS% -e Mgmt2010 -i "InputScript.rdl.rss" -s %REPORTSERVER% -v envPath=%3 -v dbName=%4 -v dbServer=%5

A look at the parameters being used

Script: The location of RS.exe which is called is Program Files\Microsoft SQL Server\110\Tools\Binn (version dependent)

Input Script (-i):We will discuss the contents of InputScript.rdl.rss in step 3. For now, it is enough to know that the parameters passed through the global variables (-v) will be accessible in the script.

Endpoint (-e): This is the optional SOAP endpoint against which the input script will be run. A set of available endpoints can be found here, but for the purposes of this example, we will continue to use the Mgmt2010 endpoint. Note that the Mgmt2005 and Mgmt2006 end points are deprecated in SQL Server 2008R2.

Server (-s): This parameter defines the web server and report server directory against which the input file should be executed. The report sever URL is a virtual directory which can be found in the RsConfig manager and usually follows the following structure

Global Variables (-v): Specifies the set of global variables to be used in the input script. We use these variables to store configuration passed through from TeamCity to allow for a single re-usable script to be used across various environments

Step 3: RSS Script

The input script is an .rss script which has access to various endpoints which will allow for the management and deployment of reports. A main function should be defined which will be called from the batch file above when the script is used as an input parameter. Below find a basic structure which defines the process of our report deployments. Note that this is defined within the Main function which will be called by the RS utility. Global variables defined in the call to RS are accessible by name.


Public Sub Main()
     Console.WriteLine("INPUT: Environment Path = {0}" , envPath)
     Dim reportDir As String = "../src/Project.Reports"
     Dim d As DirectoryInfo = New DirectoryInfo(reportDir)
     // Generate Required Directories
     // Generate Data Sources
     For Each File As FileInfo In d.GetFiles("*.rsd")
          // Generate Shared Data Sets
     Next
     For Each File As FileInfo In d.GetFiles("*.rdl")
          // Generate Reports
     Next
End Sub

All function calls to the SOAP endpoint can be can be accessed using the RS class which is available through the script (e.g. by calling RS.CreateFolder(folder, parent, properties) anywhere in your .rss script).

A comprehensive set of available functions can be found here (note that the available set of calls will differ based on the endpoint you have selected).

Following the definitions put forward for the ReportingService class, you will be able to automatically create the required folders, data sets and data sources as well as create reports (These can all be achieved using the CreateCatalogItem, SetItemDataSource and CreateFolder method calls).