SSRS Automated Deployment RS.EXE

Hi,

Recently I came across a situation where my colleague DBA’s were quite troubled uploading RDL’s to the SSRS portal, with me churning out more and more SSRS (SQL Server Reporting Services) RDL’s, and we decided to come out with an automated solution to deploy the same (Overwrite RDL’s) and new.

Well at times I am helpless business loves reports so much and they need so much more.

Any ways so when i went out to investigate the options, some were plain.

• Give the DBA’s the visual studio, share the VS solution and ask the deployer’s to deploy the RDLs via the same.

Well that idea didn’t appealed to neighter of us, so we again went out hunting and ended up with, RS.exe tool for SSRS deployment tool shipped along with MSSQL stack.

So to sum up the story we leveraged the RS.exe tool and built the relevant scripts which I though might be very handy for few other troubled as we were some time back and greatly help many to automate their SSRS deployment. This mechanism can be leveraged by the automated deployment mechanism as well for continuous integration purposes.

The key features of this systems are as follows:

The RS.exe tool is shipped by MSBI stack and can be used to upload RDL files to the report server (via web services as I read out from MSDN), and can also set the other parameters related to RDL like security and connections, which is orchestrated by the VB script (termed as RSS) for the same.

So the key element of this entire system is the RSS file which i have scripted to upload the RDL’s to the desired report server (in a desired report server folder) picked up from the file system folder (all the files with extension as RDL) and using the map file set the db connections for each RDL.

The deploy pack design:

In the above all the RDL that needs to be deployed are at the root level of the folder, and the rss folder contains the rss script which is as follows:

'SQL 2014 RS.EXE
'Location: C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn
'--Begin Script

Public srcFolderPath As String = "C:\Tools\RDL" 'Location of the deploy pack where the RDL and the folder "map" and "rss" are present
Public  destFolderPath As String = "/Sample" 'Location of the folder in report server where the RDL needs to get placed
Public	sharedDataSourcePath As String = "/Data Sources" 'Location of the folder where the shared data sources exists in the report server 

Public mapFilePath As String = srcFolderPath + "\map\map.txt"

Public Sub Main()

        Dim definition As [Byte]() = Nothing
        Dim warnings As Warning() = Nothing
		
        Dim rptdirinfo As System.IO.DirectoryInfo

        rptdirinfo = New System.IO.DirectoryInfo(srcFolderPath + "\")

        Dim filedoc As FileInfo()

        filedoc = rptdirinfo.GetFiles("*.rdl")

        For rptcount As Integer = 0 To filedoc.Length - 1

			Console.WriteLine("")
            Console.WriteLine("Uploading RDL:" + filedoc(rptcount).Name)
			Console.WriteLine("")
			
			Try
			
				Dim stream As FileStream = File.OpenRead(filedoc(rptcount).FullName)
				definition = New [Byte](stream.Length - 1) {}
				stream.Read(definition, 0, CInt(stream.Length))

				warnings = rs.CreateReport(filedoc(rptcount).Name.ToString.Replace(".rdl",""), destFolderPath, True, definition, Nothing)

				If Not (warnings Is Nothing) Then
					Dim warning As Warning
					For Each warning In warnings
						Console.WriteLine(filedoc(rptcount).Name.ToString.Replace(".rdl","") + " - " +warning.Message)
					Next warning
				Else
					Console.WriteLine("Report: {0} PUBLISHED!", filedoc(rptcount).Name.ToString.Replace(".rdl",""))
				End If
				
				UpdateDataSources_report(filedoc(rptcount).Name.ToString.Replace(".rdl",""))
				
			Catch ex As Exception
				Console.WriteLine(filedoc(rptcount).Name.ToString.Replace(".rdl","") + " - " + ex.Message)

				
			End Try

        Next
		
		Console.WriteLine("")
		Console.WriteLine("----------------------------------------")
        Console.WriteLine("SSRS Upload Finished Successfully")
		Console.WriteLine("----------------------------------------")

End Sub


 'Utility to Update The Data Sources on the Server
  
 Public Sub UpdateDataSources_report(ReportName as string)
  
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials
  
 Try
  
     Dim dataSources() as DataSource = rs.GetItemDataSources(destFolderPath + "/" + ReportName)
     
     For Each ds as DataSource in dataSources
  
        Dim sharedDs(0) as DataSource
  
        sharedDs(0)=GetDataSourceForReport(sharedDataSourcePath, ds.Name, ReportName) 
        rs.SetItemDataSources( destFolderPath + "/" + ReportName, sharedDs)

        Console.WriteLine("Set " & ds.Name & " datasource for " & "/" +  ReportName & " report")
  

     Next
	
	Console.WriteLine("")
    Console.WriteLine("All the shared data source reference set for report {0} ", ReportName)
     
 Catch goof As SoapException
  
 Console.WriteLine(goof.Detail.InnerXml.ToString())
  
 End Try
  
 End Sub

 'Function to Reference Data Sources
  
 Private Function GetDataSourceForReport(sharedDataSourcePath as string, dataSourceName as String, ReportName as String) as DataSource
  
     Dim reference As New DataSourceReference()
  
     Dim ds As New DataSource
  
     reference.Reference = sharedDataSourcePath & "/" & GetDataSourceFromMapFile(ReportName, dataSourceName) 
  
     ds.Item = CType(reference, DataSourceDefinitionOrReference)
  
     ds.Name = dataSourceName
  	 
	 Console.WriteLine("")
     Console.WriteLine("Attempting to Link Data Source {0}", ds.Name)
  
     GetDataSourceForReport=ds
  
 End Function

 Function GetDataSourceFromMapFile(rptName As String, dataSourceName As String) As String
	Dim TextLine As String
	Dim txtArr as Object()
	If System.IO.File.Exists( mapFilePath ) = True Then

		Dim objReader As New System.IO.StreamReader(mapFilePath)

		Do While objReader.Peek() <> -1

			TextLine = objReader.ReadLine()
			txtArr = TextLine.ToString.Split(",")
			
			if(txtArr(1) = dataSourceName And txtArr(2) = rptName)
				GetDataSourceFromMapFile = txtArr(0)
				Exit Function
			End if
		
		Loop
	Else
		Console.WriteLine("File Does Not Exist")
	End If
End Function

And the map folder contains a text file containing the following description:

Sample

Report Server Shared Data Source1,RDL Data Source1,Report Name1
Report Server Shared Data Source2,RDL Data Source2,Report Name1
Report Server Shared Data Source,RDL Data Source,Report Name2

Note: A single RDL can contain multiple data sources, like from DB, Cube and others. Hence a single RDL can be mapped to multiple data sources.

And finally once every things are in places and configured the only command line that is required to kick off the process in action is at a command prompt (admin) run the following command:

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn>rs.exe -i “Location of the RSS file” -s http://Location of the target report server/ReportServer

Sample:
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn>rs.exe -i “C:\Tools\RDL\rss\deploy.rss” -s http://server-name/ReportServer

Hope the above greatly reduces the troubles of SSRS automated deployment.
Download Sample Deployment Pack

One can further refine the scripts upto their requirements using the following guides on MSDN:

MSDN: Sample Reporting Services rs.exe Script

Hope it helps !!!