How to Automate Report Deployment Using SSRS Web Service

If you are using SQL Server Reporting Services much, then you probably have moved a number of reports into one RS folder or another. You most likely have done this using the deploy process within a Business Intelligence Development Studio project or used the "Upload" button within a RS folder. Using these two different methods to manage the reports in your folder is a manual process. Microsoft has provided another method where you can programmatically manage your Reporting Services environment. That other method is to use the "rs" utility. In this article, I will explore how you can use this utility to publish and extract reports for Reporting Services, as well as how to move reports from one Reporting Services folder to another.

What is the rs Utility?

The rs utility is a command line utility that is driven by a Visual Basic (VB) .NET script file that can be used to manage your Reporting Services environment. The VB script file can exploit the Report Services web service to perform many different methods against your Reporting Services environment, or across multiple environments. Here is the basic syntax for how to call the rs utility from a command prompt:

rs {-?}
[-iinput_file=]
[-sserverURL]
{-uusername}
{-ppassword}
{-ltime_out}
{-bbatchmode}
{-vglobalvars=}
{-eendpoint}
{-ttrace}

The "-i" option is used to identify the VB script file that will control how this utility manages your Reporting Services environment.

The "- s" option is used to identify the default instance of the Reporting Services environment that the VB script will run against.

The "-v" option is used to identify parameters that can be passed to the VB script file. Using this option allows you write a single VB script file that can have logic to run differently depending on the parameters passed.

The options mentioned above are the only options I use in my examples in this article. If you really want to know more about the other options I did not mention and how they can be used, please refer to the Books Online documentation.

You can do many different things with this utility. I am only going to show you a few examples. By building on to these examples, you can create your own processes that use the rs utility and the Reporting Service web service to make it easier to manage your Reporting Services environment.

Creating a Report in a Reporting Services Folder using an RDL definition

In this example, I will show you how to take an RDL file that lives in a Windows folder, and deploy it to a Reporting Services folder. This might be something you want to do if someone gave you a report definition by providing you the RDL file.

This example will be deploying a report named "MyReport", where the RDL file is named "MyReport.rdl". This RDL file resides in a windows folder named "C:tempMyRDL". In addition to this RDL file, there are two other things needed to make this work: the RSS file, and a command shell script that executes the rs utility.

Let me first talk about the command shell script that will execute the rs utility. Here is my command shell script for this example:

SET targetFolder=/MyReports
SET sourcePath=C:TempMyRDL
SET reportName=MyReport
rs.exe -i Deploy_rdl_file.rss -s http://MyServer/ReportServer
-v sourcePATH="%sourcePath%"
-v targetFolder="%targetFolder%"
-v reportName="%reportName%"

Note: the rs.exe line above was split into multiple lines for readability

In this script, I first defined three environment variables: targetFolder, sourceFolder and reportName. The "targetFolder" variable defines the folder where the report will be deployed in the Reporting Service environment. The "sourceFolder" variable is used to identify the Windows folder where the RDL file can be found, for the report being deployed. Note that two backslashes () are used for every slash, and the folder ends with two backslashes. The last variable, "reportName", identifies the name of the RDL file, less the RDL extension. This variable is also used to identify the name of the report once it is deployed to the Reporting Services environment.

The last line of this script, which appears like four lines above for readability, invokes the rs utility. A number of different options or switches are passed to this utility. The "-i "option identifies the name of the VB script that will be used to deploy the report, more about this script in a minute. This script can be found in the current directory where this command script is invoked. The next option "-s" identifies the serverURL where the Reporting Services web service can be found. The last three options are pass using the "-v" option. The "-v" option allows you to use three different environment variable settings as parameters into the VB Script file.

Now that you know how to call the rs utility, let's explore the VB script that will be used to deploy "MyReport" to the "MyReports" folder in my Reporting Services environment. The RSS file is the VB script file that programmatically tells the rs utility how to find and deploy the report. Here is my RSS file for this example, and it is called "Deploy_rdl_file.rss":

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
Try
Dim stream As FileStream = File.OpenRead(sourcePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
warnings = rs.CreateReport(reportName, targetFolder, True, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub

This script file is extremely simple and uses a number of ReportingServices2005 namespace and FileStream methods in order to deploy my RDL file. It first defines a couple of items to hold the report definition and warnings that might happen when deploying the RDL file. Inside the "Main" subroutine, it first opens up the RDL file using the FileStream OpenRead method. The source of the RDL file location is identified by concatenating the "sourcePath" and "reportName" parameters together and then tacking on the ".rdl" extension. Note how the "sourcePath" and "reportName" parameters are not declared within this VB script file. The rs utility automatically defines these parameters when they are identified on the rs.exe invocation using the "-v" option. This RDL file definition is read into a definition item by using the FileSteam .Read method. The report is then created using the ReportingServices2005 "CreateReport" method. This method is passed the target location for where to deploy the report definition by using the "reportName", and "targetFolder" parameters. The third parameter to this method, which is "True", indicates to overwrite the report if it already exists in the target folder. The last parameter is used to identify any properties that might go along with the report. In my example above no properties are identified.

This example was quite simple and showed you a simple method for how to use the rs utility to deploy a single RDL file definition from a directory into a folder in the Reporting Services environment. The next example is a little more involved.

Copying all Reports in a Reporting Services Folder to another Reporting Services Folder

In this example, I will copy all of the reports from one Reporting Services folder to another folder. To perform this copy, I will have to develop a new VB RSS file to read each report definition from one folder and create the report in another folder. In addition to the VB code, I will also need a command shell script to call the rs utility that is slightly different then my last example. Here is that command shell script for this example:

set sourceFolder=/Dev
set targetFolder=/Prod
rs.exe -i "Move_EntireDirectory_Report.rss"
-s http://MyServer/ReportServer
-v sourceFolder="%sourceFolder%"
-v targetFolder="%targetFolder%"

Note: the rs.exe line above was split into multiple lines for readability

In this command shell script, I set two different environment variables. The first environment variable, "sourceFolder", identifies the Reporting Services folder that contains the source for the reports I want to copy. The second environment variable is "targetFolder", which identifies the destination where the reports will be copied. Like the prior example, the "-i" option is used to identify the name of the VB script file to control how to copy the reports, and the "-s" option is used to identify the Reporting Services instance to use. The source and target folder are identified by using the "-v" parameters.

Here is my VB code for this example:

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim folderItems As CatalogItem()
Dim folderItem As CatalogItem
Dim message As String
Public Sub MaiN()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' process through folder
folderItems = rs.ListChildren(sourceFolder, false)
' Go through folder one item at a time
For Each folderItem In folderItems
' only items that are reports are deployed
If folderItem.Type = 2 Then
GetSourceReportDefinition()
CopyReportToTarget()
End If
Next
End Sub
Public Sub GetSourceReportDefinition()
Try
definition = rs.GetReportDefinition(sourceFolder + "/" + folderItem.Name)
Catch e As IOException
Console.WriteLine(e.Message)
End Try
message = "Got report definition for report: " & folderItem.Name
Console.Writeline(message)
End Sub 'GetSourceReportDefinition
Public Sub CopyReportToTarget()
Try
warnings = rs.CreateReport(folderItem.Name, targetFolder, True, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
message = "Copied Report: " & folderItem.Name
Console.WriteLine(message)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub 'CopyReportToTarget

This code consists of three different parts: Main, GetSourceReportDefinition, and CopyReportToTarget. The "Main" subroutine controls the flow of the VB script. It processes through the sourceFolder one item at a time. Those items are identified by using the ListChildren method. This method, lists all of the items in the folder, not just the reports. Therefore, this code uses the "Type" property of each folderItem read to determine if it is a report. A folder item is a report if its Type property is set to a "2". If the folderItem being processed in the "For Each" loop is a report then the GetSourceReportDefinition is called, followed by the CopyReportToTarget subrountine. The GetSourceReportDefinition subroutine uses the GetReportDefinition method to retrieve the report definition from the source folder. That report definition is then used in the CopyReportToTarget subroutine to create the report in the target folder using the CreateReport method. Each item in the sourceFolder is examined one at time until all items have been processed. Only those items that are reports are copied. That is all it takes to copy reports from one folder to another.

Keep in mind this process only copied the report definitions. Therefore, if any of the reports copied use a shared data source you might also have to move that, or create it in the target folder in order for your reports to work.

Better Reporting Services Management through Custom Code

As you can see the ReportingServices2005 name space, the rs utility and different VB script files allows you to do a number of things to your Reporting Services environment in an automated fashion. I am sure you have already realized the rs utility is just a simple interface that uses the ReportingServices2005 namespace. By exploiting this namespace with .NET functionality, you can build a fully automated, menu driven tool to manage multiple Reporting Services environments.

Additional Resources

MSDN Reporting Services Web Service Library
MSDN Reporting Services Web Service Library
Business Intelligence with Microsoft SQL Server Reporting Services - Part 3 -Using Reporting Server Web Services

» See All Articles by Columnist Gregory A. Larsen