Tuesday, April 10, 2012

Download All Report Definitions

I was recently at a client site who, through a bit of a long story, didn’t have any local copies of their RDL files. However, they did have all their reports deployed to a server in native mode. The question became – what’s the easiest way to download all the RDL files? Fortunately, the answer was pretty easy – an RS.exe script.

RS.exe is a command line environment which gives you a reference to the SSRS web service. You can use this to do most everything on an SSRS server, including security, rendering reports, manage subscriptions, and, fortunately, download files. You simply provide a script file written in VB.NET.

The script file will automatically have a variable named "rs" which is the reference to the Reporting Services web service. You simply provide a Main method as the starting point and away you go.

The script to download all reports and save them to the same file structure looks like this.

Dim rootPath As String = "C:\Reports"

Sub Main()
Dim items As CatalogItem() = _
rs.ListChildren(
"/", true)

For Each item As CatalogItem in items
If item.Type = ItemTypeEnum.Folder Then
CreateDirectory(item.Path)
Else If item.Type = ItemTypeEnum.Report Then
SaveReport(item.Path)
End If
Next
End Sub

Sub CreateDirectory(path As String)
path
= GetLocalPath(path)
System.IO.Directory.CreateDirectory(path)
End Sub

Sub SaveReport(reportName As String)
Dim reportDefinition As Byte()
Dim document As New System.Xml.XmlDocument()

reportDefinition
= rs.GetReportDefinition(reportName)
Dim stream As New MemoryStream(reportDefinition)

document.Load(stream)
document.Save(GetLocalPath(reportName)
+ ".rdl")
End Sub

Function GetLocalPath(rsPath As String) As String
Return rootPath + rsPath.Replace("/", "\")
End Function

I've included a variable called RootPath. This can be updated to point at whatever fold you'd like to store your reports in.


The Main method retrieves the list of all children (reports, folders, etc.) on the server. It then loops through each item. If it's a folder, it creates the folder locally. If it's a report it downloads it by first grabbing the byte array, then storing it in a memory stream and saving it locally using the XmlDocument object.


To use the script, simply copy/paste into notepad and save it, or download it from below. Then you can call RS from the command-line in SQL as follows:


rs -s http://<server>/ReportServer -i <filename>


Where <server> is the name of your Report Server, and <filename> is the name of the script file. Let it run for a few minutes and you'll have all your RDL files ready to start importing into projects.


So if you don't have the RDL files for your reports locally, downloading them is as easy as using RS.exe.


8 comments:

  1. Nice article. Saved a lot of time.

    Thanks.

    ReplyDelete
  2. Thanks, great code. Had to modify the code to run FOR loop twice. Once to create folders and then again to add files. Current code gave error when the folder didn't exist before trying to create file. Probably because I also had added filter to only download on one folder: rs.ListChildren("/", true )

    ReplyDelete
    Replies
    1. Yeah, I could have included a bit of code to ensure the root folder existed first. Was moving a bit too fast. ;-)

      Glad you enjoyed the article!

      Delete
  3. Is there a way to check to see if the file from the DB is newer then what is in the folder?

    So if Report A is newer then the file in the folder I want to overwrite the file. However, there will be a scenario where the file in the folder is newer than what is in the DB. So in that case I wouldn't want to overwrite the file.

    Is there a way to compare dates of the files? What is the best way to not overwrite a file that is newer in the folder location?

    ReplyDelete
  4. Fantastic!!!Saved an hour of my time... :)
    And learnt a new thing too!!!
    A great utility altogether!!! :)

    ReplyDelete
  5. Many thanks my deer sir!
    I feel like I owe you a beer for this one. Drop me a line if you find yourself around Tel-Aviv :)

    ReplyDelete
  6. This was a very helpful tool. One question though, I get through about 1/2 of my folders on the server and I get an unhandled exception that the permissions granted are insufficient to perform the operation. I have full permissions on the home folder of the SSRS server. Any ideas?

    ReplyDelete