Background
I needed to deploy some SSRS reports for an application we are writing and I wanted to make sure the entire application is deployable through build scripts. When working with SSRS reports I know many get accustomed to deploying using Visual Studio and that is just not an acceptable method for a production deployment. I did some research and with the help of my colleague Ray Barley I was able to the following.
The solution I have is based off the RS Utility for Reporting Services. Go here to get some good background
on it. As well, all of the code is based off of these sample scripts.
My scenario is simple I need to create a folder, then create a shared data connection and then deploy five reports. The challenge I ran into was how to deploy the .rds data connection file that in Visual Studio because I had become accustomed to that being deployed for me. We could not find anything at the moment that would deploy the file however the RS Utility allows for one to be created on the fly.
The way this works is some VB.net code is written that will call the Reporting Services web service to complete the operations that I need. The VB.net code is placed into a .rss file which can be called via command line. What we did is create three .rss files for each of the major operations we need to complete and then created a little master .cmd file that drives the whole deployment. This should be pretty re-usable for future projects.
Deploy Folder
The following code will create a folder in Reporting Services and is in a file named deploy_folder.rss.
'
' Deploy SSRS Folder
'
' Sample Command Line:
'
' rs -i deploy_report.rss
' -s http://<server>/reportserver
' -v FOLDER="Test Deploy"
'
' FOLDER name of the folder
'
Dim parentFolder As String = "AdventureWorks Sample Reports"
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
Try
rs.CreateFolder(FOLDER, "/", Nothing)
Console.WriteLine("Parent folder {0} created successfully", FOLDER)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Deploy Data Source Integrated
Here is the code to deploy a data source that is using Windows Integrated Security and is in a file called deploy_datasource_integrated.rss.
'
' Deploy SSRS DataSource Integrated
'
' Sample Command Line:
'
' rs -i deploy_datasource.rss
' -s http://<server>/reportserver
' -v EXTENSION="SQL"
' -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
' -v NAME="OperationsDataMart"
' -v FOLDER="/dev/Data Sources"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
Try
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = CONN
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = EXTENSION
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Prompt = Nothing
definition.WindowsCredentials = False
rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", NAME)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Deploy Data Source Stored
The following code is for doing a deployment of a datasource where the credentials are stored. This will support using a Windows or SQL Server account. The code is put in a file called deploy_datasource_stored.rss.
'
' Deploy SSRS DataSource Stored
'
' Sample Command Line:
'
' rs -i deploy_datasource.rss
' -s http://<server>/reportserver
' -v EXTENSION="SQL"
' -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
' -v NAME="OperationsDataMart"
' -v FOLDER="/dev/Data Sources"
' -v ISWINDOWS=True/False
' -v USER="CORPORATE\rbarley"
' -v PASSWORD="**********"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
Try
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Store
definition.ConnectString = CONN
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = EXTENSION
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Prompt = Nothing
definition.WindowsCredentials = ISWINDOWS
definition.UserName = USER
definition.Password = PASSWORD
rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", NAME)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Deploying a Report
Finally this is the code that will deploy a report. The code is in a file named deploy_folder.rss.
'
' Deploy SSRS Report
'
' Sample Command Line:
'
' rs -i deploy_report.rss
' -s http://<server>/reportserver
' -v PATH="C:\\Projects\\OperationsDataMart\\SSRS\\"
' -v REPORT="WeeklyOperations"
' -v FOLDER="/dev/WeeklyOperationsReports"
'
' PATH is the folder on the local hard drive where the report .rdl file exists. You must use \\
' for every backslash in the path; you must have a trailing backslash (to make this script
' simpler)
'
' REPORT is the file name of the report; do not include the .RDL extension
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
Try
Dim stream As FileStream = File.OpenRead(PATH + REPORT + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
warnings = rs.CreateReport(REPORT, FOLDER, True, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", REPORT)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
Running the Code
The following is a simple cmd file will create a folder, create a stored data connection using a windows account and upload five reports. Pretty simple.
set FOLDER=DemoReports
set SSRS_URL=http://localhost/reportserver
set DATA_SRC_NAME=DemoReportsConnection
set DB_CONNECTION=Data Source=localhost;Initial Catalog=Operations;Connect Timeout=60
set DB_EXTENSION=SQL
set DB_IS_WINDOWS_CONNECTION=True
set DB_USERNAME=CORPORATE\jason
set DB_PASSWORD=Pass@word1
set RDL_PATH=C:\\Demo\\Reports\\
rs -i deploy_folder.rss -s %SSRS_URL% -v FOLDER="%FOLDER%"
rs -i deploy_datasource_stored.rss -s %SSRS_URL% -v EXTENSION="%DB_EXTENSION%" -v CONN="%DB_CONNECTION%" -v NAME="%DATA_SRC_NAME%" -v FOLDER="/%FOLDER%" -v ISWINDOWS=%DB_IS_WINDOWS_CONNECTION% -v USER="%DB_USERNAME%" -v PASSWORD="%DB_PASSWORD%"
rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="DepartmentReport"
rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="ExecutiveReport"
rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="AccountingReport"
rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="FinanceReport"
rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="RollUpReport"
Posts
