SSRS RS.EXE Utility

Problem

What is the SQL Server Reporting Services RS.EXE utility? How can we use it with SSRS?  Check out this tip to learn more.

Solution

The SQL Server Reporting Services RS.EXE utility is a command line utility that can perform many scripted operation related to SQL Server Reporting Services (SSRS). It can be used to complete various administrative tasks including publishing reports and moving reports from one server to another server. Of course, with it being a command line utility, the RS.exe utility can be scripted and automated, and requires the use of a input file to tell the script “what to do”. The list of actual tasks that can be performed is quite endless and includes:

1. Deploying / Publishing reports
2. Moving reports
3. Exporting reports to a file
4. Adjust security
5. Cancel a running job
6. Configure SSRS system properties

Of course, as with any tools there are limitations and restrictions.

• First, prior to SQL Server 2008 R2, the RS utility was only available for use with a stand alone installation of SSRS; with 2008 R2 forward, the utility can be used for both stand alone / native mode installations and SharePoint integrated installations.
• Second, you must have proper permissions on the report server machine you are attempting to run the script against.
• Third, you must have permission to make the changes requested in the script file.

SQL Server Reporting Services RS.EXE Utility

The RS.exe program is generally found in the default installation directory: Tools> Binn directory; for my SQL2012 installation the file was located in: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn. As you can see in the below screen print, this directory actually contains several of the executable files used in the configuration and running of SQL Server Reporting Services.

As with most executable files run at the command line, you can request help by using the “-?” argument after the command itself, as shown below.

Based on the above screen print let us discuss each of the arguments that potentially could be added to your script.  Some, of course are straight forward where as others need additional explanation.

• Required arguments
• -i : denotes the input file, including the full or relative path, which will be run by RS.exe. This file must be written in Visual Basic .Net code and must have an rss extension.
• -s : denotes the SSRS server URL, such as http://myserver/ReportServer or https://myserver/ReportServer. The http:// or https:// prefix is optional.
• Optional arguments
• -u : user name or domain\user name of the account which will connect to the report server.
• -p : password for the specified user name entered for the -u argument; you must provide a password if the -u argument is used. If the -u and -p argument are not specified, then the currently logged in windows account is used.
• -e : defines the web services endpoint to be used.
• -l : denotes the time out for the connection. 0 is unlimited while the default is 60 seconds.
• -b : tells the utility to run all the script commands as a batch similar to the begin transaction and Commit / Rollback process in SQL. The default is to run and commit each command as it is run.
• -v : allows for the declaration of one or more variables. The syntax used to specify variables is: ” -v x=”myreports1″ y=”myreports2″. The variable names can contain numbers, letters (alphabetic), or underscores and must being with an alpha character or an underscore; the names also cannot contain Visual Basic reserved words. Generally, the syntax and permissible values follows the same rules as for Visual basic.
• -t : using this argument tells the rs.exe utility to log errors to the SSRS trace file. The trace file normally resides at %\Microsoft SQL Server\\Reporting Services\LogFiles. 

Script to Deploy a Report to SSRS with the RS.exec Utility

Remember the input file must be a VB.NET file which includes all the task to be performed. Any variables which are passed in the command line text do not have to be declared within the VB script itself. The first script we will utilize will deploy a report to our report server. The actual script command that needs to be run is included at the top of the script file, as displayed below. The command includes 3 variables:

1.  REPORTNAME for the actual name of the file to be deployed
2.  REPORTSERVER_FOLDER which describes the folder where the file should be deployed to
3.  FILEPATH denotes the location of the rdl file.

Furthermore, the actual command also includes the name of the script file (-i argument) and the server name (-s argument). The remainder of the file is the VB script which will actually write the file to the report server; notice we named the file: PUBLISHREPORT.rss. The script includes some error checking (warnings) which will write to the console along with a final statement stating the report is published or returning the error message. The script actually deploys the report using the filestream method in VB (http://www.databasejournal.com/features/mssql/article.php/3802241/Working-with-FILESTREAM-using-VB-NET.htm) 

' Script to deploy report to report server
' EXECUTE VIA COMMAND LINE
' "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe" -i C:\tools\PUBLISHREPORT.rss -s "localhost/ReportServer_SQL2012" -v REPORTNAME="Customers_Near_Stores" -v REPORTSERVER_FOLDER="/AdventureWorks Sample Reports" -t -v FILEPATH="C:\tools\\" 

DIM definition As [Byte]() = Nothing
DIM warnings As Warning() = Nothing

Public Sub Main()
 Try
 DIM stream As FileStream = File.OpenRead(FILEPATH + REPORTNAME + ".rdl")
 definition = New [Byte](stream.Length) {}
 stream.Read(definition, 0, CInt(stream.Length))
 
 warnings = rs.CreateReport(REPORTNAME, REPORTSERVER_FOLDER, 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!", REPORTNAME)
 End If
 
 Catch e As IOException
 Console.WriteLine(e.Message)
 End Try
End Sub

Running the actual command is pretty straight forward. Notice in the below screen print that the report is published, and a message is noted that the report uses a shared data source which does not exist. (We could use another script to deploy the data source).

The final result of the script is we now have a new report published on the report server, all done at the command line.

Export a Report with an SSRS Subscription with the RS.EXE

Now let us turn to a second script example which will export a report, like an SSRS subscription. This script also takes 3 variables:

1. FILENAME which is the path and file name where the export should be saved
2.  REPORTSERVER_FOLDER which is the location / folder of the report on the report server
3. FORMAT which is the file format to be exported, such as EXCEL and PDF.

Finally, the VB script is running through the process of running the report and then exporting it via the LoadReport, Render, and  FileStream methods. The file ultimately gets saved to the c:\tools directory. 

' Script to export report results
' EXECUTE VIA COMMAND LINE' 
' "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe" -i C:\tools\EXPORTREPORT.rss -s "localhost/ReportServer_SQL2012" -v FILENAME="c:\tools\stores_near_me.pdf" -v REPORTSERVER_FOLDER="/AdventureWorks Sample Reports/Customers_Near_Stores" -t  -v FORMAT="PDF" -e Exec2005


Public Sub Main()

TRY

 DIM historyID as string = Nothing
 DIM deviceInfo as string = Nothing
 DIM extension as string = Nothing
 DIM encoding as string
 DIM mimeType as string = "application/Excel"
 DIM warnings() AS Warning = Nothing
 DIM streamIDs() as string = Nothing
 DIM results() as Byte

 rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 rs.LoadReport(REPORTSERVER_FOLDER, historyID)
    
 results = rs.Render(FORMAT, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)

 DIM stream As FileStream = File.OpenWrite(FILENAME)
 stream.Write(results, 0, results.Length)
 stream.Close()
 
Catch e As IOException
  Console.WriteLine(e.Message)
End Try

End Sub

The report, in PDF format, has now been exported successfully, as displayed in the next screen print.

Checking the directory, we now see a pdf report with the name noted in the variable, and subsequently opening up the pdf displays the report.

 

We could easily change the format to Excel by just switching the FORMAT variable to “EXCEL”.             

Conclusion

The RS.exe utility brings command line scripting to SSRS. The utility allows for the completion of many administrative and reporting tasks including report deployment, report exporting, and SSRS configuration. Certain arguments are required when running the utility including the input file and the SSRS server URL. The input file is the actual Visual Basic .Net script which includes the commands to be performed; the script can accept variables which are passed as part of the script arguments and then utilized by the VB code. Other arguments which can be included with the RS.exe command include specifying the user name and password used to connect to the report server, specifying the timeout, and turning on/off the logging of the script results to the SSRS log file. The RS.exe command provides a convenient and customizable command line utility for SSRS.

Next Steps

• Use the RS.exe Utility to copy the contents of a report server to another server – http://msdn.microsoft.com/en-us/library/dn531017(v=sql.110).aspx 
• Check out these additional RS.EXE resources:
  • Remove a SQL Server Reporting Services Report from Cache Using RS.EXE
  • Migrating SQL Reporting Services to a new server
  • How To Enable Caching in SQL Server Reporting Services (SSRS)