Introduction
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.
Background
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
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:
- 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.
- Click the "SetURL" button. (If you are compiling the code, if you change the defaults, this won't be necessary.)
- 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.)
- The Select button will move the selected tree item into the
ListView
below, the DeSelect button will remove selected items from the ListView
.
- Clicking the "Migrate" button will move items from the source server to the destination server.
- Shared DataSources and Linked Reports will not be overwritten if they exist.
- Most parent items will be created if they don't exist - such as:
- Source Reports for a Linked Report
- Shared DataSources
- Shared Schedules for execution, history, or snapshots
- Security is not migrated
- Subscriptions are not migrated
- If a report is set to render from a snapshot, but there is no schedule for the snapshot, a snapshot will be taken.
- If a report has a subreport, the subreport will not be automatically migrated.
- 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
String
s are the WSDL paths to each server. The ImageIndex
s 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:
- The properties are the same as the first four parameters to
New
:
DevURL
StageURL
TrainURL
LiveURL
- The
Enum
SeverityLevels
- defines severity of error messages output by the migration.
- The Class
RSItemNode
- an overloaded version of Windows.Forms.TreeNode
. This is what the getTreeNode
method returns.
- 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 SubItem
s.
- 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:
TreeView1.Nodes.Add(maSQLRep.getReportTree("/"))
Using info from a selected TreeNode:
ListView1.Items.Add(TreeView1.SelectedNode.Tag)
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
For Each item As ListViewItem In ListView1.Items
warnings.AddRange(maSQLRep.MigrateDev2Stage(item.Text))
If Not warnings Is Nothing Then
For Each warning As maSQLReport.SQLReportingServices.Warning In warnings
ListBox1.Items.Add(warning.Message)
Next
End If
warnings.Clear()
Next
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.
History
Version 1.0 - Article is based on.