Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Reporting Services (SSRS) RS.EXE Utility


By:   |   Read Comments (18)   |   Related Tips: > Reporting Services Administration

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.

RSEXE

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.

RS Help

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).

Publish Report

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

Report Server

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.

Export Report Success

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.

File Created

pdf 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 


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 29, 2016 - 8:37:24 AM - nils Back To Top

 Hi Naimish,

to fix the issue do the following, than it should work:

To fix the issue, please replace the following code

definition = New [Byte](stream.Length) {}

with

definition = New [Byte](stream.Length - 1) {}

 


Monday, August 22, 2016 - 11:16:20 AM - Eduardo Back To Top

Hello, I was getting the invalid content error:

"....or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: '.', hexadecimal value 0x00, is an invalid character...."

Made some research and found that arrays in vb are created as N+1 long size, and filled with 0x00, so you need to change the script to:

 definition = New [Byte](stream.Length - 1) {}

 to get it working.

 

 


Tuesday, December 29, 2015 - 4:30:10 PM - Scott Murray Back To Top

I am not familiar with that error.  It appears that it cannot find asmx file.  I would check to see where that exists.


Tuesday, December 29, 2015 - 4:04:19 PM - Flowers Back To Top

 Thanks for the tip. I am able to fix the error.

But, I am getting one new error related to "Object moved". Tried with reportingservice201.asmx as endpoint. Plz help.

 

System.Net.WebException: The request failed with the error message:

--

<html><head><title>Object moved</title></head><body>

<h2>Object moved to <a href="https://portal2007.ftitools.com/sites/F03025_Colum

us_Review/_layouts/1033/error.aspx?ErrorText=The%20file%20%27%2F%5Fvti%5Fbin%2F

eportServer%2FReportService2005%2Easmx%27%20does%20not%20exist%2E">here</a>.</h

>

</body></html>

 

--.

   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapCli

ntMessage message, WebResponse response, Stream responseStream, Boolean asyncCa

l)

   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String method

ame, Object[] parameters)

   at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecure

ethods()

   at Microsoft.ReportingServices.ScriptHost.Management2005Endpoint.PingService

String url, String userName, String password, String domain, Int32 timeout)

   at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecur

ty()

   --- End of inner exception stack trace ---

   at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecur

 


Tuesday, December 29, 2015 - 2:37:16 PM - Scott Murray Back To Top

I would review the rdl file to make sure it is in the correct format.  It seems like the file is being considered another file type.

 

 


Tuesday, December 29, 2015 - 1:06:54 PM - Flower Back To Top

Thanks for the nice article. This saves lot of time. I tried to use the script to deploy RDL to sharepoint intergrated mode.
I am getting the error message as below. Can you please help."Variable specification filepath is not if the forname = Value".Can you please help me in fixing this error.

 

Thanks.

 

 


Thursday, November 06, 2014 - 5:00:24 PM - scott Back To Top

SSRS uses SSDT in 2012 forward.  All my testing for this tip was using SSRS 2012 files generated by SSDT for 2012. Have you tried opening the file in SSDT and preview it is what I am asking?  Outside of the error message, which says you have an invalid character, I cannot tell you why the upload is failing. You likely would want to start with the error message and see what is on the line mentioned.  I am very much aware of what Report Builder is and about the differences between the version. I am not sure about your Report Builder comments though; RB3.0 is not an advanced designer tool for SSDT (which is Visual Studio based). Finally, have you tried to just upload the report via the browser?


Thursday, November 06, 2014 - 2:59:20 PM - Naimish Back To Top

Thanks for reply.

No idea about SSDS.

My question is simple. The report createding RB1.0 can uploaded to SSRS using RS.exe utitliy but report from RB 3.0 can't. Why? and how can fix this?

Report Builder 3.0 is advance version of Report Designer.  In Report Builder 1.0 is older version shiped with SQL 2005, you can create report by drag n drop. RB1.0 need data model as a datasource. In RB 3.0 you can levearage SQL command. 


Thursday, November 06, 2014 - 11:54:53 AM - scott Back To Top

Have you tried using a file created in SSDS?  Not sure about report builder 3.0.


Wednesday, November 05, 2014 - 5:05:13 PM - Naimish Back To Top

Hi Scot,

Your artical is very helpful, straight foward and easily understandable.  I could upload Report.RDL file created in Report Builder 1.0 but I can't upload report created in Report Builder 3.0 with SQL Statement. I get below error. I tried both reporting server 2008 and 2012. Could you please advise. 

 

 

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>rs.exe -i D:\rs\PUBLISHREPORT.rss -s "http://localhost/ReportServer_MSSQLSERVER2012" -v REPORTNAME="MOH" -v REPORTSERVER_FOLDER="/Reports" -t -v FILEPATH="D:\rs\\"

Unhandled exception:

The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: '.', hexadecimal value 0x00, is an invalid character. Line 2173, position 10.

Many thanks

Naimish


Thursday, October 09, 2014 - 9:22:16 AM - Scott Murray Back To Top

J

 

2008 should work similar to 2012... the error indicates a problem with your report path location. I would fix that first..


Thursday, October 09, 2014 - 8:31:52 AM - J Back To Top

I try to follow the example to export a ".rdl" to PDF and failed. 

I got error like

- Invalid endpoint type

- error BC30456: 'LoadReport' is not a member of 'Microsoft.SqlServer.ReportingServices2005.ReportingService2005'.

- error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005.ReportingService2005'.

- Unhandled exception:
The path of the item 'C:\Users\***\Documents\Visual Studio 2008\Projects\Report Project1\Report Project1\MCQAuditTest.rdl' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash.

I guess I don't understand it enough on how rs.exe, *.rss, *.rdl, reportserver, ... etc are tie together.

Do you have a guide on how to export a ".rdl" report that I build using Microsoft Visual Studio 2008.  The ".rdl" report takes on 3 parameters.  I would like to run the ".rdl" report and export the result to PDF.  I would like to be able to do it via command line and schedule it.

Thank you for you help in advance,

 


Wednesday, July 02, 2014 - 10:17:02 PM - Scott Back To Top

I thiink I understand now.... try this.

http://stackoverflow.com/questions/10939714/ssrs-run-all-parameter-options

 

 


Wednesday, July 02, 2014 - 6:42:08 PM - Juan Pablo Berrios Back To Top

Thks, but is not what i'm lookig for, i have this script

Public Sub Main()

  Dim format as string = "EXCEL"

  Dim fileName as String = "C:\Jpberrios\Salida.xls"

  Dim reportPath as String = "/Reporte_trxs/Reporte_trxs"

 

  ' Prepare Render arguments

  Dim historyID as string = Nothing

  Dim deviceInfo as string = Nothing

  Dim extension as string = Nothing

  Dim encoding as string

  Dim mimeType as string

  Dim warnings() AS Warning = Nothing

  Dim streamIDs() as string = Nothing

  Dim results() as Byte

 

  rs.LoadReport(reportPath, historyID)

 

  results = rs.Render(format,  deviceInfo, extension, _

   mimeType, encoding,  warnings, streamIDs)

 

  ' Open a file stream and write out the report

  Dim stream  As FileStream = File.OpenWrite(fileName)

  stream.Write(results, 0, results.Length)

  stream.Close()

End Sub

 

so, report has user value param, but i need execute script passing user value.

 

hoping you understand me.

regards.


Tuesday, July 01, 2014 - 7:03:19 AM - Scott Back To Top

Sorry posted the wrong link...http://stackoverflow.com/questions/14286457/using-parameters-in-batch-files-at-dos-command-line


Monday, June 30, 2014 - 8:07:14 PM - Scott Back To Top

Not sure where exactly you want to pass in the parameters, but at the dos level, you may want to check:

 

http://www.mssqltips.com/sqlservertip/3255/sql-server-reporting-services-ssrs-rsexe-utility/

 

 


Monday, June 30, 2014 - 6:20:12 PM - Juan Pablo Berrios Back To Top

 

Please can you help me, using this script using user parameter??


Wednesday, June 25, 2014 - 11:41:27 AM - Girijesh Pandey Back To Top

Very informative!

 

rs.exe can also be used for deployment of SSRS report using rss  file (not rdl ) created by rs scripter tool.


Learn more about SQL Server tools