Using RS Scripter to create deployment script for reporting services

Update: The download link and the sqldbatips site seems to be down for quite sometime now and I am not sure if it’s going to revive ever or not. I came across a project on CodePlex which talks about installing and configuring Reporting Services Scripter (RS Scripter) as part of the SQL Server install process, not sure how well it’s going to work but here is the link to the project -

SQL Server FineBuild

Install Reporting Services Scripter

In case you are not able to find the executable for RSScripter look at http://search.4shared.com/q/1/rsscripter (if it’s still there Fingers crossed)


If you are using SSRS then chances are high to come across a scenario where you want to deploy reports developed on development machine to the production server, there are various ways to do this and one of them is to use RS Scripter tool. Here are the steps to do this -

1. Creating Report Deployment Script

  1. Download the Reporting Services Scripter - http://www.sqldbatips.com/showarticle.asp?ID=62
  2. Update the servers.xml; change the reportservice value if your ReportServer name is different.

    <server label="SQL2000" reportservice="http://localhost/ReportServer/ReportService.asmx" />

    <server label="SQL2005" reportservice="http://localhost/ReportServer/ReportService2005.asmx" />

  3. Run the RSScripter.exe.
  4. Click on Options, and select the options selected in images below( for more information about Scripting Options check the readme file - http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_scripting_options) –

    clip_image001

    clip_image002

    clip_image003

  5. Change the SQL 2005 RS EXE Location in the Global tab as per the location of SQL server and Apply –

    clip_image004

  6. Select SQL 2005 from the Report Server dropdown and click on Get Catalog, all the reports, data source, Schedules, Roles etc. present in report server will be displayed –

    rs1

  7. Select all the reports, data source, schedules and roles you want to transfer.

    rs2

Change the output directory path (Should not be machine/user specific e.g. Desktop) e.g. C:\AClickReports and click on Script. The script for loading the specified items will be generated to the specified folder.

2. Deploying the Reports

  1. Ensure that you have IIS and dot net framework 2.0 is installed on report server.
  2. While installing SQL server 2005 select reporting services option.
  3. Follow the report server configuration steps and make sure "Report Server Configuration Manager" is all green ticks?

    clip_image008

  4. Follow these steps to publish reports –
    1. Extract the AClickReports folder to the report server, C: Drive.
    2. Open the “RS Scripter Load All Items.cmd” file located in AClickReports folder for editing.
    3. Change the SCRIPTLOCATION Script variable value to the location where AClickReports folder is copied (Should be same to the output directory path selected in step 3.8)

        SET SCRIPTLOCATION=C:\ AClickReports\

      clip_image009

    4. Change the RS Script variable value to the location where Microsoft SQL Server is installed (if it is different from the default C drive).

      SET RS="C:\Program Files\Microsoft SQL Server\90\Tools\Bin\RS.EXE"

    5. Run the RS Scripter Load All Items.cmd batch file, it will publish the reports to the report server.
  5. Follow these steps to verify that reports are deployed correctly

    1. Go to http://localhost/Reports/Pages/Folder.aspx,

      clip_image011

    2. Open the Data Sources you should see data sources selected in step 1.7

      clip_image013

    3. Open the data source and update the data source details as per the configuration of report server(if they are different) and apply.

      rs3

    4. Go back to Home page and click on the report folder selected in step 1.7 , select one of the reports and click on Edit button, then click on the Data Sources link ; Make sure that the data source is configured properly.

      clip_image017

    5. Repeat the steps from 2.5.2 to 2.5.4 for other data sources and report folders.

I have used this tool successfully for more then one and half years to provide report deployment script to client with every release without facing any major issue. I would highly recommend you to use this for deploying your reports on client machines.

26 Comments

  • Looks like a nice tool. Do you have support for forms authentication.

  • Thanks for the article. I tried this approach and it works great except for the datasource. While generating the batch file, I am selecting the shared datasource and the rdl files. After running the batch file, I see the datasource along with the data files but while running the report, it gives a message "Cannot connect to datasource. Login failed for sa". Deleting the existing shared datasource and adding it again and remapping it to reports, makes it work again. Any ideas on how to get rid of the problem. The name of the datasource matches the data source embeded in the rdl file, not sure why its happening. Any ideas will be appreciated.

  • Hi,
    Just wondering, whether this will affected the current reports and its access?? I need to copy the reports from one server to other and at the same time should not affect the current one.
    I tried copying a test one and its not working with the tool and giving error while deploying, I have a server with 900 reports and I want to use this tool.
    Its saying overwrite shared data source, what that mean??
    Please advice.

  • Hi,
    I tried to deploy the reports.. But the foldername and filename that had spaces in it is not deploying.Its giving an error saying it can find the file.
    Any suggestions?

    Thanks

  • Hi All,
    I fixed the issue with SQL 2008, we need to change the .cmd line. Need to repalce %RS% with RS. We dont want to set a variable for the location of the rs.exe. In sql 2008 we have rs.exe as rsutility and it will automatically execute. I redeployed 900 reports from sql2005 to sql 2008 which saves much time.
    Thank you for this script...
    really working.
    Thanks
    Bismi

  • @bisjom: Thanks for the update, will definitely be helpful for others. Now a days I am not in touch with SSRS so I was not able to help you on this.

  • After the Migration of SSRS reports.I need to edit the DataSource.I scripted the Datasource and edit the .rs and .rss files.And Loaded the scirpts and it works fine.Is it the right approach i am doing.let me know.I have lot of datasource which needs to be edited.Do let me know if there are any other standard approach,or with this tool.

    Thanks,

  • I am having a issue with reports that when i am trying to open that report with 'https url' then it do not show me the images where as when i am using http url then it is working fine and showing me the images.
    Can you suggest any solution to my problem....

  • Dear team,

    When i try to publish the reports using RS Scripter Load All Items.cmd batch file,the reports are not publishing in reporting server.

    I ma getting the error as:
    Error : rsItemNotFound (The item '/XXXX Reports' cannot be found.)

    and data source is creating successfully.

  • This is really a nice tool for reports deployment.

    I have a problem with subscritions deployment from one server to other server.

    Couls you please let me know how we can deploy using this script.

    Thanks in advance.

  • I am trying RS Scripter for the first time. I manage several SSRS 2008 R2 instances with several hundred reports. Quite a few of these reports have subscriptions and data driven subscriptions. We also have a few shared datasets.

    The scripter seems to work fine with reports and shared data sources. However my shared datasets do not get deployed and none of the subscriptions are coming over. Any ideas on how I can accomplish having the shared datasets and subscriptions scripted properly.

    Thanks in advance for any input you may have.

  • Is this export report's subscription from 2005 to 2008 also? We have subscription associated with most of the reports.

  • Hello, I am trying to use RSScripter and i get the error "The underlying connection was closed: Unable to connect to remote server". What does this mean?

  • eporting Services Scripter Load Log 2.0.0.17

    Starting Load at Mon 11/14/2011 15:51:10.74
    SCRIPTLOCATION = D:\Reports\
    REPORTSERVER = http://localhost/ReportServer
    BACKUPLOCATION =
    SCRIPTLEVEL = SQL2005
    TIMEOUT = 60
    RS = "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RS.EXE"

    Running script "D:\Reports\Data Sources\Data Sources.fldr.rss"
    Folder /Data Sources already exists and cannot be overwritten
    The command completed successfully

    Running script "D:\Reports\SMReports\SMReports.fldr.rss"
    Folder /SMReports already exists and cannot be overwritten
    The command completed successfully

    Running script "D:\Reports\Data Sources\DataSource1.rds.rss"
    Error : rsItemAlreadyExists (The item '/Data Sources/DataSource1' already exists.)
    The command completed successfully

    Running script "D:\Reports\SMReports\CPU_Daily.rdl.rss"
    The data set `DataSource1' refers to the shared data source `DataSource1', which is not published on the report server.
    Report DataSources set successfully
    The command completed successfully

    Running script "D:\Reports\SMReports\Top10Reports_UDP.rdl.rss"
    The data set `DataSource1' refers to the shared data source `DataSource1', which is not published on the report server.
    Report DataSources set successfully
    The command completed successfully


    Finished Load at Mon 11/14/2011 15:53:03.03

  • Hi,

    I am using ur steps to do deployment of reports. but i am getting below errors.

    pls let me know. How i can solve this error.

    Reporting Services Scripter Load Log 2.0.0.17

    Starting Load at Mon 11/14/2011 15:51:10.74
    SCRIPTLOCATION = D:\Reports\
    REPORTSERVER = http://localhost/ReportServer
    BACKUPLOCATION =
    SCRIPTLEVEL = SQL2005
    TIMEOUT = 60
    RS = "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RS.EXE"

    Running script "D:\Reports\Data Sources\Data Sources.fldr.rss"
    Folder /Data Sources already exists and cannot be overwritten
    The command completed successfully

    Running script "D:\Reports\SMReports\SMReports.fldr.rss"
    Folder /SMReports already exists and cannot be overwritten
    The command completed successfully

    Running script "D:\Reports\Data Sources\DataSource1.rds.rss"
    Error : rsItemAlreadyExists (The item '/Data Sources/DataSource1' already exists.)
    The command completed successfully

    Running script "D:\Reports\SMReports\CPU_Daily.rdl.rss"
    The data set `DataSource1' refers to the shared data source `DataSource1', which is not published on the report server.
    Report DataSources set successfully
    The command completed successfully

    Running script "D:\Reports\SMReports\Top10Reports_UDP.rdl.rss"
    The data set `DataSource1' refers to the shared data source `DataSource1', which is not published on the report server.
    Report DataSources set successfully
    The command completed successfully


    Finished Load at Mon 11/14/2011 15:53:03.03

  • How we can deploy or generate the shared dataset using the above utility

  • I too am trying to use RSScripter to deploy shared datasets. It seems this is an issue from the comments. How can this be done?

  • How to use localhost:8086 instead of localhost

  • I am trying to generate deployment scripts for SSRS 2008 R2.

    I tried using a third part tool "RS Scripter" but it does not generate any scripts for shared datasets and linked reports.

    I am using the label: SQL Server 2008 and it uses http://localhost/ReportServer/ReportService2005.asmx

    I have tried various options and even though the RS Scripter says it can script out linked reports I am unable to script them. I can only see two options related to linked reports in the tool -
    a. OverwriteLinkedReport
    b. TransferLinkedReportSize
    Both these options are set to true.

    I am using 2.0.0.17 version of RS Scripter.

    The only option that seems to work for shared datasets is to use http://localhost/ReportServer/ReportService2010.asmx and programatically add the shared datasets to the report server. Once I add the shared dataset they don't seem to have the reference to the shared data source therefore, I have to set references programmatically. But this is very time consuming as I have too many objects that I have to deploy. Do we a have a simpler and efficient way?

    I will really appreciate any help/suggestion.

    Thanks,
    Sugandha

  • I am trying to generate deployment scripts for SSRS 2008 R2 and I am encountering the following problem -

    Background

    I have reached a point where I am able to generate scripts for

    - Folder Structures

    - Shared DataSets

    - Shared DataSources

    - Linked Reports

    - Reports

    Copying the .rds and .rdl through script did not set references to shared datasource and shared datasets respectively. Therefore, I had to explicitly set references for both shared datasets and reports through scripts / C# Code.

    Now, when I run the reports that are deployed as a result of these scripts, I get the following error –

    An error has occurred during report processing. (rsProcessingAborted)

    One or more parameters required to run the report have not been specified. (rsParametersNotSpecified)

    I tried debugging this error by creating a C# console application that has reference to SSRS Web Service (ReportingService2010). On calling GetItemParameters, I noticed that all parameters that are query based have a ParameterState “Dynamic Values Unavailable”.

    Questions

    - I am not sure what is missing here and more importantly how can I correct this problem?

    - Also, is there a simpler & efficient way to generate deployment scripts for SSRS 2008 R2? I am currently using a third part tool RS Scripter to generate some basic scripts. Then I have to manually edit scripts because the tool does not script out shared datasets & linked reports.

    Thanks,

    Sugandha

  • when i run the batch file from my machine it is showing the error Could not connect to server: http://localhost/Reportsserver/ReportService2005.asmx can u suggest on this pls

  • Hi, the script is working fine but its not copying report level security.

    Thanks,
    Ravi

  • SQL Server Reporting Services utility “rs.exe” is failing when executed from the command line. &nbsp;All reports that process lesser volume of data complete successfully. &nbsp;However, when it processes about 350 queues of data, then the failure occurs.

    Error: “The underlying connection was closed: An unexpected error occurred on a receive”

    RS Command Used: rs.exe -i C:\UC4\Agents\windows\Temp\JOBS.EXP.SSRS.CLAIMS_INVENTORY_AFTER_PURGE.0001606024.rss -s http://ucdbrpc1/ReportServer -e Exec2005 -l 0

  • I have loads of reports to migrate from SSRS 2005 to 2008 R2, but the link to download the RSScripter tool doesn't work any more. Grateful if someone who got the files to make it available to the community and send over a link.
    Thanks a miles !!

  • The download link is not available now, any kind man can send me a copy of this software? thanks very much.

    Email: folgerfu@gmail.com

  • Download RSScripter from here

    execsql.org/rs-scripter-tool-handy-tool-to-create-deployment-script-for-reporting-services

Add a Comment

As it will appear on the website

Not displayed

Your website