title: Dynamic deploy of SSRS reports using rs.exe
date: 2012-03-01 14:14:00 +0200 +0200
draft: false
author: John

This script will search for reports in the local folder SOURCEREPORTFOLDER and then deploy them in the RS folder TARGETRSFOLDER, creating subfolders if needed. If any of the reports already exists in RS it will first do a backup of the existing report into the local folder BACKUPLOCATION. The target folder in RS will contain the same file structure as the source folder. Subfolders will be created if they don’t already exists and so on. All actions are logged to a log file for each deploy.

This script expects only one datasource for all reports. If the datasource has a different name in the target environment than where it was developed you will get an error in the log when the report first is deployed, but the script will after that set the proper data source and it will work anyway. The reason for this is that the report expects a data source with a certain name and the script can only set the correct data source after the report has been deployed. If you are confident that the script works (after extensive testing) you can suppress these messages by setting the variable SuppressWarnings to true in the rss file but I am not sure if thats a good idea.

I tried to create a unique log file name for each deploy but since date and time is acting a bit weird when running batch scripts I resorted to having a random number in the end of the log file. Not pretty but it works. All you need to do to test the script is to modify the the beginning of the batch script. The variables need to be set according to your needs.

How to test the scripts:

  1. Save both scripts (DeployReports.cmd & DeployReports.rss) into the folder of your choice. In your selected folder, create two subfolders named Reports and Log.
  2. Put the reports you want to deploy into the Reports folder.
  3. Create the “root” folder where the reports should be deployed in RS. Also create the datasource before deploying the reports.
  4. Edit the bat to adapt the variables to your environment. The lines you need to edit is highlighter below.
  5. Run the bat file in a command prompt, on the machine where RS is running.

I added some debug features in the rss script which helped me while developing the script and if you want to turn debug on there is a variable called DebugComments which you can set to true. The scripts should work on both Reporting Services 2005 and 2008.


@echo on
:: ** Script generated by Reporting Services Scripter **
:: ** Created by Jasper Smith (jas@sqldbatips.com)             **
:: ** See http://www.sqldbatips.com for help/support           **
::Requires Reports and Log folder to work
::Script Variables
SET REPORTSERVER=http://localhost/ReportServer
SET RS="C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe"
::Generate suffix for log and backup
@For /F "tokens=1,2,3 delims=/ " %%A in ('Date /t') do @( 
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set Dat=%%C%%B%%A
@For /F "tokens=1,2 delims=: " %%A in ('time /t') do @( 
Set Hour=%%A
Set Minute=%%B
Set tim=%%A%%B_r%random%
SET LOGFILE="log\DeployReports_%dat%_%tim%.log"
SET BACKUPLOCATION=Backup\Bak_%dat%_%tim%\\
::Write Log Header
ECHO Reporting Services Scripter Load Log >>%LOGFILE%
ECHO Starting Load at %DATE% %TIME% >>%LOGFILE%
ECHO Variables:
ECHO RS                  = %RS% >>%LOGFILE%
::Run Scripts
ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%


' Script to publish reports from a local folder, 
' keeping the folder structure in RS as the local folder
'''' Common folder path and data source for all reports
'''' Target folder and datasource must be configured 
'''' before publishing any reports
'Where are the reports you want to publish located?
Dim SourceReportFolder As String = vSOURCEREPORTFOLDER
' Into what folder in RS do you want the reports to be published? 
Dim TargetRSFolder As String = vTARGETRSFOLDER 
'Which datasource should the reports use? 
'Must have been created before deploying!
Dim TargetDataSource As String = VTARGETDATASOURCE 
'What is the original datasource of the reports when developing? 
'Should not change that often.
Dim OriginalReportDataSource As String = vORIGINALDATASOURCE 
'''' Below this line is static variables and should 
'''' not be touched when publishing reports
'Debug help
Dim SupressWarnings As Boolean = False
Dim DebugComments As Boolean = True
'Other variables
Dim sFolder As String = SourceReportFolder
Dim di As New DirectoryInfo(SourceReportFolder)
Dim DirectoryFolders As DirectoryInfo() = di.GetDirectories()
Dim FilesFoundInFolder As FileInfo() = di.GetFiles()
Dim Folder As DirectoryInfo
Dim CurrentSourceDir AS String = SourceReportFolder 
Dim CurrentTargetDir As String = TargetRSFolder
Dim TotalNumberOfReports As Integer = 0
Dim TotalNumberOfFolders As Integer = 0
Public Sub Main()
    Console.Writeline("Target folder: /" + TargetRSFolder)
    Console.Writeline("Target datasource: /" + TargetRSFolder + "/" + TargetDataSource)
    Console.Writeline("Processing source folder: " + SourceReportFolder)
    Console.Writeline("Reports published: " & TotalNumberOfReports)
    Console.Writeline("Subfolders: " & TotalNumberOfFolders)
End Sub
Public Sub PubReportsAndFolders(ByVal FolderToParse As String)
    CurrentSourceDir = FolderToParse
    CurrentTargetDir = FolderToParse.Replace(SourceReportFolder, "")
    CurrentTargetDir = CurrentTargetDir.Replace("\", "/")
    Dim ParsedFolder As New DirectoryInfo(CurrentSourceDir)
    Dim DirectoryFolders As DirectoryInfo() = ParsedFolder.GetDirectories()
    Dim DirectoryFiles As FileInfo() = ParsedFolder.GetFiles()
    Dim Folder As DirectoryInfo
    Dim FileInFolder As FileInfo
    Console.Writeline(" ")
    FilesFoundInFolder = ParsedFolder.GetFiles()
    If DebugComments Then
        Console.Writeline("Folder start: " + FolderToParse)
        Console.Writeline("CurrentSourceDir: " + CurrentSourceDir)
        Console.Writeline("CurrentTargetDir 1: " + CurrentTargetDir)
    End If
    For Each FileInFolder In FilesFoundInFolder
        If DebugComments Then
            Console.WriteLine("Deploying report " + FileInFolder.Name + " start")
        End If
        Dim TempName As String = FileInFolder.Name
        Dim name As String = TempName.Replace(".rdl", "")
        Dim location As String = CurrentSourceDir + "\" + TempName
        Dim parent As String = "/" + TargetRSFolder + CurrentTargetDir
        Dim varDataSourceReference As String = "/" + TargetRSFolder + "/" + TargetDataSource
        Dim fullpath As String = parent + "/" + name
        Dim overwrite As Boolean = True
        Dim reportContents As Byte() = Nothing
        Dim warnings As Warning() = Nothing
        Dim descprop As New [Property]
        descprop.Name = "Description"
        descprop.Value = ""
        Dim hiddenprop As New [Property]
        hiddenprop.Name = "Hidden"
        hiddenprop.Value = "False"
        Dim props(1) As [Property]
        props(0) = descprop
        props(1) = hiddenprop
        Dim ReportFound As Integer = 0
        Dim srcReportCond As New SearchCondition
        With srcReportCond
            .Condition = ConditionEnum.Equals
            .Name = "Name"
            .Value = name
        End With
        Dim srchReportResults As CatalogItem() = Nothing
        srchReportResults = rs.FindItems _
         (parent, BooleanOperatorEnum.And, _
        New SearchCondition() {srcReportCond})
        Dim srchReportResult As CatalogItem
        For Each srchReportResult In srchReportResults
            If srchReportResult.Type = ItemTypeEnum.Report Then
                ReportFound = 1
            End If
        If DebugComments Then
            If ReportFound = 0 Then
                Console.Writeline("Report NOT found: " + parent + "/" + name)
                Console.Writeline("Report FOUND: " + parent + "/" + name)
            End If
        End If
        If DebugComments Then
            Console.Writeline("Backup section start: " & fullpath)
        End If
        If (ReportFound = 1) Then
                Dim exists As [Property]() = RS.GetProperties(fullpath, Nothing)
                Dim ActualBackupLocation As String = BACKUPLOCATION
                ActualBackupLocation = ActualBackupLocation & FolderToParse.Replace(SourceReportFolder, "")
                ActualBackupLocation = ActualBackupLocation.Replace("\\", "\") & "\"
                ActualBackupLocation = ActualBackupLocation.Replace("\\", "\") & "\" 'To always get \\ at the end
                If DebugComments Then
                    Console.Writeline("Actual backup location: " & ActualBackupLocation)
                End If
                If Not (System.IO.Directory.Exists(ActualBackupLocation)) Then
                End If
                Dim fsReport As New System.IO.FileStream(ActualBackupLocation + Path.GetFileName(location), IO.FileMode.Create)
                Dim RepDef() As Byte = RS.GetReportDefinition(fullpath)
                fsReport.Write(RepDef, 0, RepDef.Length)
            Catch e As SoapException
                Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
            End Try
            If DebugComments Then
                Console.Writeline("Report do not exist, no backup needed.")
            End If
        End If
        If DebugComments Then
            Console.Writeline("Backup section complete")
        End If
            Dim stream As FileStream = File.OpenRead(location)
            reportContents = New [Byte](stream.Length - 1) {}
            stream.Read(reportContents, 0, CInt(stream.Length))
            warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)
            If Not (warnings Is Nothing) Then
                Dim warning As Warning
                For Each warning In warnings
                    If SupressWarnings Then
                        Console.WriteLine("Report ""{0}"" published", name)
                        Console.WriteLine("Report ""{0}"" published with warnings", name)
                        Console.WriteLine("Warning: " + Warning.Message)
                    End If
                Next warning
                Console.WriteLine("Report ""{0}"" published successfully with no warnings", name)
            End If
            Dim DataSources(0) As DataSource
            '' DataSourceReference "DataSourceRef" is the same as the name of the datasource in the report
            '' DataSource "DataSrs" is the name of the actual deployed datasource in RS
            Dim DataSourceRef As New DataSourceReference
            DataSourceRef.Reference = "/" + TargetRSFolder + "/" + TargetDataSource
            Dim DataSrs As New DataSource
            DataSrs.Item = CType(DataSourceRef, DataSourceDefinitionOrReference)
            DataSrs.Name = OriginalReportDataSource
            DataSources(0) = DataSrs
            rs.SetItemDataSources(parent + "/" + name, DataSources)
            Console.WriteLine("Datasource configured: {0}", DataSourceRef.Reference)
            If DebugComments Then
                Console.WriteLine("DataSrs.Name: " + DataSrs.Name)
            End If
        Catch Excep As IOException
        Catch Excep As SoapException
            Console.WriteLine("Error: " + Excep.Detail.Item("ErrorCode").InnerText + " (" + Excep.Detail.Item("Message").InnerText + ")")
        End Try
        TotalNumberOfReports = TotalNumberOfReports + 1
        Console.Writeline(" ")
    If DebugComments Then
        Console.Writeline("Searching for subfolders in'" + FolderToParse + "'...")
    End If
    Console.Writeline(" ")
    For Each Folder In DirectoryFolders
        CurrentTargetDir = FolderToParse.Replace(SourceReportFolder, "")
        CurrentTargetDir = CurrentTargetDir.Replace("\", "/")
        CurrentTargetDir = "/" + TargetRSFolder + CurrentTargetDir
        Dim TargetFolderItems As CatalogItem() = Nothing
        If DebugComments Then
            Console.Writeline("Local folder to parse: " + FolderToParse)
            Console.Writeline("Current target dir: " + CurrentTargetDir)
        End If
        TargetFolderItems = rs.ListChildren(CurrentTargetDir, False)
        If DebugComments Then
            Console.Writeline("CurrentTargetDir 2: /" + CurrentTargetDir)
            Console.Writeline("TargetFolderItems count: ")
        End If
        Dim x As Integer = 0
        For i As Integer = 0 To TargetFolderItems.Length - 1
            If (TargetFolderItems(i).Type = 1) Then
                x = x + 1
            End If
        Next i
        Dim myFolderArray(x - 1) As String
        For i As Integer = 0 To TargetFolderItems.Length - 1
            If (TargetFolderItems(i).Type = 1) Then
                myFolderArray(x - 1) = TargetFolderItems(i).Name
                If DebugComments Then
                End If
                x = x - 1
            End If
        Next i
        Dim FolderFound As Integer = 0
        Dim srcFolderCond As New SearchCondition
        With srcFolderCond
            .Condition = ConditionEnum.Equals
            .Name = "Name"
            .Value = Folder.Name
        End With
        Dim srchResults As CatalogItem() = Nothing
        srchResults = rs.FindItems _
         (CurrentTargetDir, BooleanOperatorEnum.And, _
        New SearchCondition() {srcFolderCond})
        Dim srchResult As CatalogItem
        For Each srchResult In srchResults
            If srchResult.Type = ItemTypeEnum.Folder Then
                FolderFound = 1
            End If
        If FolderFound = 0 Then
            rs.CreateFolder(Folder.Name, CurrentTargetDir, Nothing)
            Console.Writeline("Created target folder: " + CurrentTargetDir + "/" + Folder.Name)
            Console.Writeline("Existing target folder found: " + CurrentTargetDir + "/" + Folder.Name)
        End If
        TotalNumberOfFolders = TotalNumberOfFolders + 1
        Console.Writeline("Processing source folder: " + FolderToParse + "\" + Folder.Name)
        PubReportsAndFolders(FolderToParse + "\" + Folder.Name)
End Sub

Since VB is not my strongest skill I am sure this can be done in a much more efficient way and the code is far from pretty, but it seems to be working as it should. I based these scripts on the output from Reporting Services Scripter.

Other web sites that helped me understand how this works (I probably forgot some):