Click here to Skip to main content
Click here to Skip to main content

A helper object to migrate SQL Reporting Service items

, 10 Sep 2004 CPOL
Rate this:
Please Sign up or sign in to vote.
A helper object library to automate migration of SQL RS items from development to production servers.


There are two main pieces of code in the solution: a helper object (maSQLReport), and a test form that uses the object to do its work. Please note that the test application (which can be downloaded as an EXE) has almost no exception handling, and is very crude. Its function was only to allow me to test prior to integration into our existing migration tool.


Our company supports an ASP/ASP.NET business application. Currently, this application uses Crystal Reports for reporting, but we are investigating SQL Reporting. Our development process has us do development and unit testing on a development server, migration and acceptance testing on a staging server, and production and training each on a dedicated server.

We already have written a tool which moves the ASP pages, SQL Procs, and crystal .RPT files for each development issue along the migration path. The goal of this object was to integrate similar migration of SQL Reports using the Reporting Services SOAP API. As such, some of the choices for public interfaces for maSQLReport are based on the needs we had in that application - such as exposing the available items in a tree.

Using the Test Application

Sample Image - maSQLReport.jpg

The demo simply tests the public interfaces of the helper object. It could serve as a guide to integrating the object into your own work.

To use:

  1. Set the WSDL path to the report servers in the "Source" and "Destination" input boxes. I have pre-filled them with an example for the default location and name.
  2. Click the "SetURL" button. (If you are compiling the code, if you change the defaults, this won't be necessary.)
  3. Click on "Load Tree". This will show you all items from the root of your source server. (Note that walking the folder tree via the web service is slow - if you have a lot of nested folders, this could take a while. You might want to change the getReportTree call to start in a sub-folder.)
  4. The Select button will move the selected tree item into the ListView below, the DeSelect button will remove selected items from the ListView.
  5. Clicking the "Migrate" button will move items from the source server to the destination server.
    1. Shared DataSources and Linked Reports will not be overwritten if they exist.
    2. Most parent items will be created if they don't exist - such as:
      1. Source Reports for a Linked Report
      2. Shared DataSources
      3. Shared Schedules for execution, history, or snapshots
      4. Security is not migrated
      5. Subscriptions are not migrated
    3. If a report is set to render from a snapshot, but there is no schedule for the snapshot, a snapshot will be taken.
    4. If a report has a subreport, the subreport will not be automatically migrated.
  6. Output results will appear in the ListBox to the right. You may clear it with the "Clear" button.

Using the helper object

First, instantiate an instance of the object. If you wish to set the paths at run-time, just set them to empty strings; as long as the property is set prior to making a call, it is fine.

 Dim maSQLRep As New maSQLReport.Reports("", "", "", "", 1, 2, 3, 4, 5, 6)

This takes the following input:

DevURL As String, StageURL As String, TrainURL As String, _
 LiveURL As String, ResourceImageIndex As Integer, ReportImageIndex As Integer, _
 DataSourceImageIndex As Integer, LinkReportImageIndex As Integer, _
 ClosedFolderImageIndex As Integer, OpenfolderImageIndex As Integer 

The xxxURL Strings are the WSDL paths to each server. The ImageIndexs are the image index of a ImageList object that has the images to use in a TreeView defined in your program.

maSQLReport exposes the helper object, Reports, as well as the actual SOAP API, as SQLReportingServices.

Object Definition:

maSQLReport.Reports exposes four public interfaces, as well as four properties, an Enum, and a class. They are:

  1. The properties are the same as the first four parameters to New:
    • DevURL
    • StageURL
    • TrainURL
    • LiveURL
  2. The Enum
    • SeverityLevels - defines severity of error messages output by the migration.
  3. The Class
    1. RSItemNode - an overloaded version of Windows.Forms.TreeNode. This is what the getTreeNode method returns.
    2. In addition to the regular Treenode method/properties, this also exposes:
      • Item - is the SQLReportingServices.CatalogItem the node is based on.
      • Tag - is populated with a ListViewItem containing the path, a type description, and the source URL as SubItems.
  4. The Methods:
    • getReportTree - Takes the starting path and an optional String for the root NodeName.
    • MigrateDev2Stage - takes a fully qualified item path, and moves that item from the server defined in devURL to the server defined in StageURL.
    • MigrateStage2Train - same as above, from StageURL to TrainURL.
    • MigrateStage2Live - same, from StageURL to LiveURL.

Example Uses:

Setting the URLs from a textbox:

maSQLRep.DevURL = txtSourceURL.Text
maSQLRep.StageURL = txtDestinationURL.Text

Getting the contents of the source server as a TreeNode:

'Assume TreeView1 is a TreeView, that has an imageList set
'returned nodes will have imageindexs set to match the defined indexes from object
'If path specified, would start there

Using info from a selected TreeNode:

'Populating a listview - if it is in detail mode, and has 3 columns
'with subitem information already loaded 
'Could also read any of the CatalogItem properties by casting to RSItemNode
Dim rsNode As maSQLReport.Reports.RSItemNode = TreeView1.SelectedNode
Dim path As String = rsNode.Item.Path

Migrating an item and displaying the results:

Dim warnings As New ArrayList 
'All Items in a listview, place output in a listbox 
For Each item As ListViewItem In ListView1.Items 
    If Not warnings Is Nothing Then 
        For Each warning As maSQLReport.SQLReportingServices.Warning In warnings
    End If 
'A single item found from casting example
warnings = maSQLRep.MigrateDev2Stage(path)

Point of Interest

At this time, there is no provision to migrate server settings, such as roles/item security, or subscriptions. Our need was just to move the actual Catalog items.

If there is interest, I will do a follow-up article that walks-through the actual object code.


Version 1.0 - Article is based on.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mark Brandon
Software Developer (Senior)
United States United States
I've been playing around with computers since I started programming in about 1979. On punch cards. (that was an Explorer program in 4th grade) I ran a BBS in the late '80s for the 4 years I was in college for the first time... but ended up in the theater department.
After a dropping out for a while, my flirtation with being a studio recording engineer lead to a BS in EET - after which I ended up somehow being a network admin / tech support person.
I then taught high school electronics for a while... before leaving for the exciting world of restaurant managment.
I gave it up, however, to get a second degree in IT/Software Design - and now the circle is complete, I'm back in programming; primarly ASP.NET / Classic ASP business apps.

Comments and Discussions

Questiondifference between enterprise edition and express edition Pinmembervaratharajan198316-Aug-07 20:38 
AnswerRe: difference between enterprise edition and express edition PinmemberMark Brandon21-Aug-07 3:57 
**WHAT** problem do you have? Does it have anything to do with using the Reporting Service API? Confused | :confused:
You are comparing the freely distributable (express) edition and the most expensive license (Enterprise). It should be no shock that there are differences in functionality - please read the Microsoft literature describing version features of the various SQL editions.
I don't know, but I would be really surprised if Express supported RS. Express is a client machine database engine to support workstation applications; it is not a server product.
In 2005 there is a client-side rendering object designed to let you render RDL based reports on the client, AFAIK, it does not require any SQL engine support. This object did not ship with 2000. I do not know if it became available for 2000 with the release of 2005 (but I doubt it), as I do not use that type of functionality.
BTW, the destination use of this object was tested against SQL Standard. While I have an install of SQL Developer (which has all Enterprise features) that I used as the source server, our production & unit/regression testing servers are only Standard. I didn't test moving a data-driven subscription, and have no way to test (or idea what this would do) if you had it running in a farm.

QuestionData exchane to MS-Excel PinmemberRavisubrahmanya21-Jul-07 3:06 
Questionhow to deploy a reporting server PinmemberPadoor Shiras19-Jul-07 21:19 
AnswerRe: how to deploy a reporting server PinmemberMark Brandon20-Jul-07 3:58 
GeneralRights errors PinmemberRZOE18-May-07 7:15 
AnswerRe: Rights errors PinmemberMark Brandon21-May-07 4:47 
Generalsystem.web.webexception Pinmemberkimmy234324322-May-07 3:04 
GeneralRe: system.web.webexception PinmemberMark Brandon21-May-07 4:52 
GeneralRe: system.web.webexception Pinmembertaersious3-Sep-09 11:23 
GeneralRe: system.web.webexception PinmemberMark Brandon3-Sep-09 11:38 
GeneralRe: system.web.webexception Pinmembertaersious3-Sep-09 11:58 
GeneralCrystal Migration PinmemberSKRIMPS6-Mar-07 9:01 
GeneralRe: Crystal Migration PinmemberMark Brandon6-Mar-07 9:39 
GeneralChanging database type at run time PinmemberAlexEvans28-Nov-06 17:16 
GeneralSecurity Settings Pinmembercolby.jones23-Mar-06 5:06 
GeneralRe: Security Settings PinmemberMark Brandon23-Mar-06 5:09 
GeneralRe: Security Settings Pinmembercolby.jones23-Mar-06 5:25 
AnswerRe: Security Settings PinmemberMark Brandon23-Mar-06 5:46 
GeneralRe: Security Settings Pinmembercolby.jones23-Mar-06 10:50 
GeneralExcellent Mark PinmemberMattias438728921-Oct-04 23:20 
GeneralRe: Excellent Mark PinmemberMark Brandon25-Oct-04 4:04 
GeneralRe: Excellent Mark PinmemberMattias438728925-Oct-04 4:54 
GeneralRe: Excellent Mark PinmemberMark Brandon29-Oct-04 16:33 
GeneralRS deployment Pinmemberre_das20-Sep-04 11:34 
GeneralRe: RS deployment PinmemberMark Brandon6-Oct-04 4:40 
GeneralSo, is there any interest PinmemberMark Brandon20-Sep-04 3:38 
GeneralRe: So, is there any interest Pinmemberjohn palmer12-Apr-05 10:48 
GeneralRe: So, is there any interest PinmemberMark Brandon20-Apr-05 6:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150327.1 | Last Updated 10 Sep 2004
Article Copyright 2004 by Mark Brandon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid