Export (0) Print
Expand All

Sample Reporting Services rs.exe Script to Migrate Content between Report Servers

This topic includes and describes a sample Reporting Services RSS script that copies content items and settings from one SQL Server Reporting Services report server to another report server, using the RS.exe utility. RS.exe is installed with Reporting Services. The script migrates Reporting Services items, for example reports and subscriptions, from server to server. The script supports both SharePoint mode and Native mode report servers.

Applies to: Reporting Services SharePoint mode | Reporting Services Native mode

In this Topic:

Download the script to a local folder. Download the script from the CodePlex site Reporting Services RS.exe script migrates content. See the section How to use the ssrs_migration.rss script in this topic for more information.

The script supports both SharePoint mode and Native mode report servers. The script supports the following report server versions:

  • SQL Server 2014

  • SQL Server 2012

  • SQL Server 2008 R2

The script can be used to copy content between report servers of the same mode or different modes. For example, you can run the script to copy content from a SQL Server 2008 R2 native mode report server to a SQL Server 2012 SP1 SharePoint mode report server. You can run the script from any server where RS.exe is installed. For example, in the following deployment, you can:

  • Run RS.exe and the script ON Server A.

  • To copy content FROM Server B

  • TO Server C

Server name

Report Server Mode

Server A

Native

Server B

SharePoint

Server C

SharePoint

For more information on the RS.exe utility, see RS.exe Utility (SSRS).

Arrow icon used with Back to Top link Top

Items and Resources the Script Migrates

The script will not write over existing content items of the same name. If the script detects items with the same name on the destination server that are on the source server, the individual items will result in a “failure” message and the script will continue. The following table lists the types of content and resources the script can migrate to target report server modes.

Item

Migrated

SharePoint

Description

Passwords

No

No

Passwords are NOT migrated. After content items are migrated, update the credential information on the destination server. For example, data sources with stored credentials.

My Reports

No

No

The Native mode “My Reports” feature is based on individual user logins therefore the scripting service does not have access to content in “My Reports” folders for users other than the –u parameter used to run the rss script. Also, “My Reports” is not a feature of Reporting Services SharePoint mode and items in the folders cannot be copied to a SharePoint environment. Therefore, the script does not copy report items that are in the “My Reports” folders on a source native mode report server

To migrate the content in “My Reports” folders with this script, complete the following:

  1. Create new folder(s) in Report Manager. Optionally, you can create folders or subfolder for each user.

  2. Login as one of the users with “My Reports” content.

  3. In Report Manager, click the My Reports folder.

  4. Click the Details view for the folder.

  5. Select each report that you want to copy.

  6. Click Move in the Report Manager toolbar.

  7. Select the desired destination folder.

  8. Repeat steps 2-7 for each user.

  9. Run the script.

Schedules

yes

yes

To migrate schedules, it is required that SQL Server Agent is running on the target server. If SQL Server Agent is not running on the target, you will see an error message similar to the following:

Migrating schedules: 1 items found. Migrating schedule: theMondaySchedule ... FAILURE: The SQL Agent service is not running. This operation requires the SQL Agent service. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SchedulerNotResponding Exception: The SQL Agent service is not running. This operation requires the SQL Agent service.

Roles and system policies

Yes

Yes

By default the script will not copy custom permission schema between servers. The default behavior is the items will be coied to the destination server with the ‘inherit parent permissions’ flag set to TRUE. If you want the script to copy permissions for individual items, use the SECURITY switch.

If the source and target servers are not the same report server mode, for example from native mode to SharePoint mode, and you use the SECURITY switch, the script will attempt to map default roles and groups based on the comparison in the following topic Compare Roles and Tasks in Reporting Services to SharePoint Groups and Permissions. Custom roles and groups are not copied to the destination server.

When the script is copying between servers that are the same mode, and you use the SECURITY switch, the script will create new roles (native mode) or groups (SharePoint mode) on the destination server.

If a role already exists on the destination sever, the script will create a “Failure” message similar to the following, and continue migrating other items. After the script completes, verify the roles on the destination server are configured to meet your needs. the Migrating roles: 8 items found.

Migrating role: Browser ... FAILURE: The role 'Browser' already exists and cannot be created. ---> Microsoft.ReportingServices.Diagnostics.Utilities.RoleAlreadyExistsException: The role 'Browser' already exists and cannot be created.

For more information, see Grant User Access to a Report Server (Report Manager)

Note: if a user that exists on the source server does not exist on the destination server, the script cannot apply role assignments on the destination server, the script cannot apply role assignments, even if the SECURITY switch is used.

Shared data source

Yes

Yes

The script will not overwrite existing items on the target server. If an item on the target server already exists with the same name, you will see an error message similar to the following:

Migrating DataSource: /Data Sources/Aworks2012_oltp ... FAILURE:The item '/Data Sources/Aworks2012_oltp' already exists. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemAlreadyExistsException: The item '/Data Source s/Aworks2012_oltp' already exists.

Credentials are NOT copied over as part of the data source. After content items are migrated, update the credential information on the destination server.

Shared dataset

Yes

Yes

Folder

Yes

Yes

The script will not overwrite existing items on the target server. If an item on the target server already exists with the same name, you will see an error message similar to the following:

Migrating Folder: /Reports ... FAILURE: The item '/Reports' already exists. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemAlreadyExistsException: The item '/Reports' already exists.

Report

Yes

Yes

The script will not overwrite existing items on the target server. If an item on the target server already exists with the same name, you will see an error message similar to the following:

Migrating Report: /Reports/testThe item '/Reports/test' already exists. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemAlreadyExistsException: The item '/Reports/test' already exists.

Parameters

Yes

Yes

Subscriptions

Yes

Yes

History Settings

Yes

Yes

The history settings are migrated however the history details are NOT migrated.

History

No

No

processing options

Yes

Yes

cache refresh options

Yes

Yes

Dependent settings are migrated as part of a catalog item. The following is the sample out of the script as it migrates a report (.rdl) and related settings such as cache refresh options:

  • Migrating parameters for report TitleOnly.rdl 0 items found.

  • Migrating subscriptions for report TitleOnly.rdl: 1 items found.

  • Migrating subscription Save in \\server\public\savedreports as TitleOnly ... SUCCESS

  • Migrating history settings for report TitleOnly.rdl ... SUCCESS

  • Migrating processing options for report TitleOnly.rdl ... 0 items found.

  • Migrating cache refresh options for report TitleOnly.rdl ... SUCCESS

  • Migrating cache refresh plans for report TitleOnly.rdl: 1 items found.

  • Migrating cache refresh plan titleonly_refresh735amM2F ... SUCCESS

Cache refresh plans

Yes

Yes

Images

Yes

Yes

Report parts

Yes

Yes

Arrow icon used with Back to Top link Top

The permissions required to read or write items and resources is not the same for all of the methods used in the script. The following table summarizes the methods used for each item or resource and links to specific topic. Navigate to the individual topic to see the required permissions. For example the ListChildren method topic notes the required permissions of:

  • Native Mode Required Permissions: ReadProperties on Item

  • SharePoint Mode Required Permissions: ViewListItems

Item or Resource

Source

Target

Catalog items

ListChildren(String, Boolean)

GetProperties(String, Property[])

GetItemDataSources(String)

GetItemReferences(String, String)

GetDataSourceContents(String)

GetItemLink(String)

CreateCatalogItem(String, String, String, Boolean, Byte[], Property[], Warning[])

SetItemDataSources(String, DataSource[])

GetItemReferences(String, String)

CreateDataSource(String, String, Boolean, DataSourceDefinition, Property[])

CreateLinkedItem(String, String, String, Property[])

CreateFolder(String, String, Property[])

Role

ListRoles(String, String)

GetRoleProperties(String, String, String)

CreateRole(String, String, String[])

System Policy

GetSystemPolicies()

SetSystemPolicies(Policy[])

Schedule

ListSchedules(String)

CreateSchedule(String, ScheduleDefinition, String)

Subscription

ListSubscriptions(String)

GetSubscriptionProperties(String, ExtensionSettings, String, ActiveState, String, String, String, ParameterValue[])

GetDataDrivenSubscriptionProperties(String, ExtensionSettings, DataRetrievalPlan, String, ActiveState, String, String, String, ParameterValueOrFieldReference[])

CreateSubscription(String, ExtensionSettings, String, String, String, ParameterValue[])

CreateDataDrivenSubscription(String, ExtensionSettings, DataRetrievalPlan, String, String, String, ParameterValueOrFieldReference[])

Cache refresh plan

ListCacheRefreshPlans(String)

GetCacheRefreshPlanProperties(String, String, CacheRefreshPlanState, String, String, ParameterValue[])

CreateCacheRefreshPlan(String, String, String, String, ParameterValue[])

Parameters

GetItemParameters(String, String, Boolean, ParameterValue[], DataSourceCredentials[])

SetItemParameters(String, ItemParameter[])

Execution options

GetExecutionOptions(String, ScheduleDefinitionOrReference)

SetExecutionOptions(String, String, ScheduleDefinitionOrReference)

Cache options

GetCacheOptions(String, ExpirationDefinition)

SetCacheOptions(String, Boolean, ExpirationDefinition)

History settings

GetItemHistoryOptions(String, Boolean, ScheduleDefinitionOrReference)

SetItemHistoryOptions(String, Boolean, Boolean, ScheduleDefinitionOrReference)

Item Policy

GetPolicies(String, Boolean)

SetPolicies(String, Policy[])

 

For more information, see Compare Roles and Tasks in Reporting Services to SharePoint Groups and Permissions.

Arrow icon used with Back to Top link Top

  1. Download the script file to a local folder, for example c:\rss\ssrs_migration.rss.

  2. Open a command prompt with administrative privileges.

  3. Navigate to the folder containing the ssrs_migration.rss file.

  4. Run the command with the parameters appropriate for your scenario.

Basic Example, native mode report server to native mode report server:

The following example migrates content from the native mode Sourceserver to the native mode Targetserver.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password"

Usage notes:

  • The script runs in two steps.

    The first step is an audit, to return a list of items that will be migrated and the second step is the migration process.

    You can cancel the script after step one if you only want to see the possible migration list or you want to modify the parameters. Dependent settings are not listed in step one. For example, the cache options of a report are not listed but the report itself is.

    A good use of the step 1 audit information is to review existing roles on both the source and target Native mode server.

    The following is an example of the step one audit list. Notice the list includes a “roles” section because the switch-v security="True" was used:

    • Retrieve and report the list of items that will be migrated. You can cancel the script after step 1 if you do not want to start the actual migration.

      Retrieving roles:

      Role: Browser

      Role: Content Manager

      Role: Model Item Browser

      Retrieve and report the list of items that will be migrated. You can cancel the script after step 1 if you do not want to start the actual migration.

      Retrieving roles:

      Role: Browser

      Role: Content Manager

      Role: CustomRole

      Role: Model Item Browser

      Role: My Reports

      Role: Publisher

      Role: Report Builder

      Role: System Administrator

      Role: System User

      Retrieving system policies:

      Retrieving system policies:

      System policy: BUILTIN\Administrators

      System policy: domain\user1

      System policy: domain\ueser2

      Retrieving schedules:

      Schedule: theMondaySchedule

      Retrieving catalog items. This may take a while.

      Folder: /Data Sources

      DataSource: /Data Sources/Aworks2012_oltp

      Folder: /images

      Resource: /images/Boba Fett.png

      Resource: /images/R2-D2.png

      Folder: /Reports

      Report: /Reports/products

      Report: /Reports/test

      Report: /Reports/TitleOnly

  • The SOURCE_URL and TARGET_URL must be valid report server URLs that point to the source and target Reporting Services report server. In native mode, a report server URL looks like the following:

    • http://servername/reportserver

    In SharePoint mode the URL looks like the following:

    • http://servername/_vti_bin/reportserver

  • The virtual folder structure presented to the user in SharePoint might be different than the underlying one. Open http://servername/_vti_bin/reportserver or http://servername/sites/site_name/_vti_bin/reportserver in a browser to see the non-virtual folder structure. This is helpful for setting source folder and target folder to something other than "/", for a server in SharePoint mode.

  • Passwords are not migrated, and must be re-entered, for example data sources with stored credentials.

Arrow icon used with Back to Top link Top

Parameter

Description

Required

-s Source_URL

URL of the source report server

Yes

-u Domain\password –p password

Credentials for source server.

OPTIONAL, default credentials are used if missing

-v st="SITE"

OPTIONAL. This parameter is only used for SharePoint mode report servers.

- v f="SOURCEFOLDER"

Set to "/" for migrating everything, or to something like "/folder/subfolder" for partial migration. Everything within this folder will be copied

OPTIONAL, default is "/".

-v ts="TARGET_URL"

'URL of the target RS server"

-v tu="domain\username" -v tp="password"

'Credentials for target server.

OPTIONAL, default credentials are used if missing. Note: the user will be listed as the “creator” of shared schedules and “modified by” account for report items, in the target server.

-v tst="SITE"

OPTIONAL. This parameter is only used for SharePoint mode report servers.

-v tf ="TARGETFOLDER"

'Set to "/" for migrating into the root level. Set to "/folder/subfolder" to copy into a that already exists. Everything within "SOURCEFOLDER" will be copied into "TARGETFOLDER.

OPTIONAL, default is "/".

-v security= "True/False"

If set to “False”, destination catalog items will inherit security setting according to the settings of the target system. This is the recommended setting for migrations between different report server types, for example native mode to SharePoint mode. If set to “True”, the script attempts to migrate security settings.

OPTIONAL, default is “False”.

Arrow icon used with Back to Top link Top

Native Mode Report Server to Native Mode Report Server

The following example migrates content from the native mode Sourceserver to the native mode Targetserver.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password"

The following example adds the security switch:

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p password -v ts="http://TargetServer/reportserver" -v tu="Domain\Userser" -v tp="password" -v security="True"

Native Mode to SharePoint Mode – root site

The following example migrates content from a native mode SourceServer to the “root site ” on a SharePoint mode server TargetServer. The “Reports” and “Data Sources” folders on the native mode server as migrated as new libraries on the SharePoint deployment.

ssrs_rss_migrate_root_site
rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p Password -v ts="http://TargetServer/_vti_bin/ReportServer" -v tu="Domain\User" -v tp="Password"

Arrow icon used with Back to Top link Top

Native mode to SharePoint Mode –‘bi’ site collection

The following example migrates content from a native mode server to a SharePoint server that contains a site collection of "sites/bi" and a shared documents library. The script creates folders in document the destination library. For example, the script will create a “Reports” and "Data Sources” folders in the target document library.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\User -p Password -v ts="http://TargetServer/sites/bi/_vti_bin/reportserver" -v tst="sites/bi" -v tf="Shared Documents" -v tu="Domain\User" -v tp="Password"

Arrow icon used with Back to Top link Top

SharePoint Mode to SharePoint Mode –‘bi’ site collection

The following example migrates content:

  • From a SharePoint server SourceServer that contains a site collection of "sites/bi" and a shared documents library.

  • To a TargetServer SharePoint server that contains a site collection of "sites/bi" and a shared documents library.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/_vti_bin/reportserver -v st="sites/bi" -v f="Shared Documents" -u Domain\User1 -p Password -v ts="http://TargetServer/sites/bi/_vti_bin/reportserver" -v tst="sites/bi" -v tf="Shared Documents" -v tu="Domain\User" -v tp="Password"

Arrow icon used with Back to Top link Top

Native Mode to Native Mode – Windows Azure Virtual Machine

The following example migrates content:

  • From a Native mode report server SourceServer.

  • To a TargetServer Native mode report server running on a Windows Azure virtual machine. The TargetServer is not joined to the domain of the SourceServer and the User2 is an administrator on the Windows Azure virtual machine TargetServer.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://SourceServer/ReportServer -u Domain\user1 -p Password -v ts="http://ssrsnativeazure.cloudapp.net/ReportServer" -v tu="user2" -v tp="Password2"
TipTip

For information on how to use Windows PowerShell to create Reporting Services report servers on Windows Azure virtual machines, see Use PowerShell to Create a Windows Azure VM With a Native Mode Report Server.

The following example migrates content:

  • From a SharePoint mode report server SourceServer that contains a site collection of "sites/bi" and a shared documents library.

  • To a TargetServer Native mode report server running on a Windows Azure virtual machine. The TargetServer is not joined to the domain of the SourceServer and the User2 is an administrator on the Windows Azure virtual machine TargetServer.

rs.exe -i ssrs_migration.rss -e Mgmt2010 -s http://uetesta02/_vti_bin/reportserver -u user1 -p Password -v ts="http://ssrsnativeazure.cloudapp.net/ReportServer" -v tu="user2" -v tp="Passowrd2"

The section summarizes some of the steps to take on the destination server to verify content and policies were successfully migrated.

Schedules

To verify schedules on the target server:

Native Mode

  1. Browse to Report Manager on the destination server.

  2. Click Site Settings on the top menu.

  3. Click Schedules in the left pane.

SharePoint Mode:

  1. Browse to Site settings.

  2. In the Reporting Services group, click Manage Shared Schedules.

Arrow icon used with Back to Top link Top

Roles and Groups

Native Mode

  1. Open SQL Server Management Studio and connect to your native mode report server.

  2. In Object Explorer click Security.

  3. Click Roles.

Use the trace flag –t to receive more information. For example, if you run the script and see a message similar to the following

  • Could not connect to server: http://<servername>/ReportServer/ReportService2010.asmx

Run the script again with the –t flag, to see a message similar to the following:

  • System.Exception: Could not connect to server: http://<servername>/ReportServer/ReportService2010.asmx ---> System.Net.WebException: The request failed with HTTP status 401: Unauthorized. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.IsSSLRequired() at Microsoft.ReportingServices.ScriptHost.Management2010Endpoint.PingService(String url, String userName, String password, String domain, Int32 timeout) at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecurity() --- End of inner exception stack trace ---

Arrow icon used with Back to Top link Top

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft