Dynamic deploy of SSRS reports using rs.exe

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.

DeployReports.cmd:
@echo on
:: ** Script generated by Reporting Services Scripter 2.0.0.17 **
:: ** 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 SOURCEREPORTFOLDER=Reports
SET ORIGINALDATASOURCE=Datasource
SET REPORTSERVER=http://localhost/ReportServer
SET TARGETRSFOLDER=LABfolder
SET TARGETDATASOURCE=Datasource

SET SCRIPTLOCATION=DeployReports.rss
SET RS="C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe"
SET TIMEOUT=60



::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 2.0.0.17 >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Starting Load at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%
ECHO Variables:
ECHO SOURCEREPORTFOLDER  = %SOURCEREPORTFOLDER% >>%LOGFILE%
ECHO ORIGINALDATASOOURCE = %ORIGINALDATASOURCE% >>%LOGFILE%
ECHO REPORTSERVER        = %REPORTSERVER% >>%LOGFILE%
ECHO TARGETRSFOLDER      = %TARGETRSFOLDER% >>%LOGFILE%
ECHO TARGETDATASOURCE    = %TARGETDATASOURCE% >>%LOGFILE%
ECHO SCRIPTLOCATION      = %SCRIPTLOCATION% >>%LOGFILE%
ECHO BACKUP              = %BACKUPLOCATION% >>%LOGFILE%
ECHO TIMEOUT             = %TIMEOUT% >>%LOGFILE%
ECHO RS                  = %RS% >>%LOGFILE%
ECHO. >>%LOGFILE%

::Run Scripts


ECHO Running script "%SCRIPTLOCATION%" >>%LOGFILE%
%RS% -i "%SCRIPTLOCATION%" -s %REPORTSERVER% -l %TIMEOUT% -v BACKUPLOCATION="%BACKUPLOCATION%" -v vSOURCEREPORTFOLDER="%SOURCEREPORTFOLDER%" -v vTARGETRSFOLDER="%TARGETRSFOLDER%" -v vTARGETDATASOURCE="%TARGETDATASOURCE%" -v vORIGINALDATASOURCE="%ORIGINALDATASOOURCE%">>%LOGFILE% 2>&1
ECHO. >>%LOGFILE%

ECHO. >>%LOGFILE%
ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%

DeployReports.rss:
' 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()
    Console.Writeline()
    Console.Writeline("Target folder: /" + TargetRSFolder)
    Console.Writeline("Target datasource: /" + TargetRSFolder + "/" + TargetDataSource)

    Console.Writeline("Processing source folder: " + SourceReportFolder)
    PubReportsAndFolders(SourceReportFolder)
    Console.Writeline("Reports published: " & TotalNumberOfReports)
    Console.Writeline("Subfolders: " & TotalNumberOfFolders)
    Console.Writeline()

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
        Next

        If DebugComments Then
            If ReportFound = 0 Then
                Console.Writeline("Report NOT found: " + parent + "/" + name)
            Else
                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
            Try
                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
                    System.IO.Directory.CreateDirectory(ActualBackupLocation)
                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)
                fsReport.Close()

            Catch e As SoapException
                Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
            End Try
        Else
            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

        Try
            Dim stream As FileStream = File.OpenRead(location)
            reportContents = New [Byte](stream.Length - 1) {}
            stream.Read(reportContents, 0, CInt(stream.Length))
            stream.Close()

            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)
                    Else
                        Console.WriteLine("Report ""{0}"" published with warnings", name)
                        Console.WriteLine("Warning: " + Warning.Message)
                    End If
                Next warning
            Else
                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
            Console.WriteLine(Excep.Message)
        Catch Excep As SoapException
            Console.WriteLine("Error: " + Excep.Detail.Item("ErrorCode").InnerText + " (" + Excep.Detail.Item("Message").InnerText + ")")
        End Try
        TotalNumberOfReports = TotalNumberOfReports + 1
        Console.Writeline(" ")
    Next

    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(CurrentTargetDir)
            Console.Writeline("TargetFolderItems count: ")
            Console.Writeline(TargetFolderItems.Length)
        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
                    Console.Writeline(TargetFolderItems(i).Name)
                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
        Next

        If FolderFound = 0 Then
            rs.CreateFolder(Folder.Name, CurrentTargetDir, Nothing)
            Console.Writeline("Created target folder: " + CurrentTargetDir + "/" + Folder.Name)
        Else
            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)

    Next

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):

1 comment