SQL Server Magazine

Reporting Services Scripter was developed by Jasper Smith, a SQL Server MVP, enterprise DBA, and the operator of www.sqldbatips.com, to make his work in a large-scale SQL Server environment easier and more efficient. Although the Microsoft SQL Server Reporting Services (SSRS) tools are very useful, they were designed for creating and managing reports on a single server. However, Reporting Services Scripter makes it easy to move reports between multiple servers and to administer the catalog item properties of various reports.

Reporting Services Scripter is a .NET Windows Forms application that enables the scripting and transfer of groups of SSRS catalog items from one server to another or from one SSRS folder to another on the same server. Reporting Services Scripter is flexible and lets you transfer catalog item properties, such as serverside report parameters, Subscriptions (both normal and data-driven), Descriptions, History options, and Execution options (including both report-specific schedule and shared schedule execution options) to other SQL Server instances. Reporting Services Scripter is well maintained and frequently updated to enhance functionality and fix problems that users encounter.

Scripting a report with Reporting Services Scripter is easy. To generate a report, simply select it from the Catalog Explorer treeview, choose the scripting options you prefer and the output folder, and click Script. You’ll typically receive three files out of this process: my_report .rdl, which is the actual report definition file; my_report.rdl .rss, which is the RSS script that loads the report and sets its properties; and RS Scripter Load All Items.cmd, which is the batch file used to generate all aspects of the report. When you want to load the scripted report onto another server, simply change the REPORTSERVER variable to the name of the target server in the RS Scripter Load All Items.cmd file and rerun it.

Reporting Services Scripter can be used to

• extract all Report Definition Language (RDL) from the report server
• automatically generate scripts to load reports, data sources, resources, linked reports, and folders, including their properties, onto the target server
• generate command files to load scripted items onto a target server
• transfer items between servers (including migrating data from SQL Server 2000 to SQL Server 2005) and folders
• generate scripts for Shared Schedules, System and Item Level Roles, and Normal and Data Driven Report Subscriptions
• generate scripts to load existing RDL files onto the target server (especially useful with the RDL files that are kept under version control)
• back up reports prior to any overwrite
• generate scripts from the command line or UI
• reverse-engineer Microsoft Visual Studio report projects from the report server

Reporting Services Scripter comes in a download called RSScripter.zip. Once you’ve unzipped Reporting Services Scripter into its own folder, such as C:\RSScripter, you need only a small handful of files (RSScripter.exe and RSScripter.cfg in particular) to get started.

Reporting Services Scripter has three modes of operation: Script, Transfer, and Generate mode. Script mode lets you run scripts that you’ve already created, Transfer mode lets you move scripts between folders and servers, and Generate mode lets you generate scripts for reports already in SSRS.

Reporting Services Scripter requires the .NET Framework 1.1 and—to be able to run the generated command files or use the Transfer mode of deploying scripts—the SSRS management tools, especially the command-line utility RS.EXE. You’ll need to have both versions of the SSRS management tools installed if you want to transfer report definitions between SQL Server versions.

Note that Reporting Services Scripter hasn’t been tested with all possible versions of SQL Server. Reporting Services Scripter also isn’t localized and has been tested only against English versions of SSRS and the required OSs.

End of Article