A while ago I posted on what now seems to be a defunct blog site, SQL Junkies, a blog about how you can use the RS scripting utility to generate SQL Server Reporting Services reports from command line prompt. In preparation for the DevTeach conference I decided to resurrect it for a demo and since a few things changed between SSRS 2000 and SSRS 2008, it required some work and digging through books online. One of the changes was that starting with SQL Server 2005, Reporting Services provides two separate web service endpoints. The ReportService2005 service is used for management and administrative functions, and the ReportExecution2005 service contains functions related to report rendering. By default, RSS script connects to the management endpoint but in this case since I wanted to use the render function, I needed to connect to the other endpoint. So, the trick here is to use the new endpoint (–e) switch parameter and specify RsExec2005 to use the execution endpoint instead.
Here is a code sample showing rendering from the command prompt, the following DOS command runs the utility against specified report server (-s parameter) and instructs it to execute a file that follows the –I parameter. The third parameter specifies the execution endpoint:
rs -i "c:\temp\reports\Render Report.rss" -s http://rrehak/ReportServer_Katmai -e Exec2005
The RSS utility will render a report (I am using one of the sample Adventure Works reports) and save the rendered file in MS Excel format. The contents of the Render Report.rss file look like this, you may need to modify to match your path to the report:
Public Sub Main()
Dim format as string = "EXCEL"
Dim fileName as String = "C:\temp\reports\Product Line Sales.xls"
Dim reportPath as String = "/AdventureWorks 2008 Sample Reports/Product Line Sales 2008"
' 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