Thursday, October 29, 2009

How to automate SSRS reports deployment or How to create a setup for SSRS Reports deployment

I'm reading: How to automate SSRS reports deployment or How to create a setup for SSRS Reports deploymentTweet this !
Today on 29th Oct 2009, I had a chance to present the use of a tool called RSScripter for deployment of SSRS Reports at London SQL Server Users Group. SQL Server MVP Jamie Thomson presented a nice session explaining use of MS Build to automate SSRS Reports deployment. In alignment to the same topic, I got a few minutes to present my knowhow on the same topic.

As documented on the product site, "Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters."

It has a very nice GUI and the options are pretty self-relevant. There are a few steps that one needs to follow to create a setup out of this utility for reports deployment.


Step 1: Configure paths for the below

a) Point to correct path of RS.exe location on the Globals Tab

b) Target report server url where you intend to deploy your SSRS Reports

c) SSRS Solution or Project file under which all the SSRS reports are added or select individual reports that you would want to target. Also you can click on the Get Catalog button to find out the reports on the server that you have configured.

d) Output location where you want the setup files to be created

e) Select Generate Report Script on the Report Tab

f) Select Specify New Parent Folder / Create Folder Hierarchy option from the General Tab as per your requirement.

g) Use other properties as per requirements, which can be used to fine tune the settings for deployment. Detailed information about the effect of using these properties can be found from the readme file of the tool, hosted on its product site.

Step 2: Click the Script button to create setup files

Step 3: Execute the setup batch file created, and this would deploy all the reports.

Behind the scenes, the batch file executes the VB Script files created for each SSRS Report. This script files contains the code, that creates a corresponding object of SSRS Reports model, and uses the RS.exe utility with these reports as parameters. This utility sets all the properties of these objects that are collected thru the user interface.

The biggest assets of this utility is its a FREEWARE and it uses VBScript, MS DOS Batch File, and RS.exe to create a setup for deployment. Also the output can be version controlled by using some version control software. The setup created by this utility is very transparent to understand, no extra assemblies (.dll) and it builds on RS.exe and VBScript which doesn't require any additional piece of code or software to interpret.

If still there is someone left, who is a fan of coding, all the information on how to create scripts using VBScript to deploy each different object of SSRS can be found on this page.


ak said...

It's a good article. The only problem I have with this is the target report server URL, I can't find it anywhere in the option dialog tabs. Or if there's any it's not obvious enough for me to see. Although I could change the script manually or pass it as a parameter.

Anonymous said...

You have a great talent of writing.Good Luck and get going.And yes i have book mark your site .

singanan Krishnasamy said...

Hi Siddarth,

Thanks for the tips. realy helpful.. any idea whether 2008 r2 compatiable version of RSSscripter is available....

Siddharth Mehta said...

RSScripter should be compatible with SSRS 2008 R2 too, but it might not work in sharepoint integrated mode.

Hmav said...

I have 1 email subscription and one windows file subscription in my list.I could not transfer only windows file subscriptions.

Related Posts with Thumbnails