Automated SSRS deployment with the RS utility
- by Stacy Vicknair
If you’re familiar with SSRS and development you are probably aware of the SSRS web services. The RS utility is a tool that comes with SSRS that allows for scripts to be executed against against the SSRS web service without needing to create an application to consume the service. One of the better benefits of using this format rather than writing an application is that the script can be modified by others who might be involved in the creation and addition of scripts or management of the SSRS environment. Reporting Services Scripter Jasper Smith from http://www.sqldbatips.com created Reporting Services Scripter to assist with the created of a batch process to deploy an entire SSRS environment. The helper scripts below were created through the modification of his generated scripts. Why not just use this tool? You certainly can. For me, the volume of scripts generated seems less maintainable than just using some common methods extracted from these scripts and creating a deployment in a single script file. I would, however, recommend this as a product if you do not think that your environment will change drastically or if you do not need to deploy with a higher level of control over the deployment. If you just need to replicate, this tool works great. Executing with RS.exe Executing a script against rs.exe is fairly simple. The Script Half the battle is having a starting point. For the scripting I needed to do the below is the starter script. A few notes: This script assumes integrated security. This script assumes your reports have one data source each. Both of the above are just what made sense for my scenario and are definitely modifiable to accommodate your needs. If you are unsure how to change the scripts to your needs, I recommend Reporting Services Scripter to help you understand how the differences. The script has three main methods: CreateFolder, CreateDataSource and CreateReport. Scripting the server deployment is just a process of recreating all of the elements that you need through calls to these methods. If there are additional elements that you need to deploy that aren’t covered by these methods, again I suggest using Reporting Services Scripter to get the code you would need, convert it to a repeatable method and add it to this script! Public Sub Main()
CreateFolder("/", "Data Sources")
CreateFolder("/", "My Reports")
CreateDataSource("/Data Sources", "myDataSource", _
"Data Source=server\instance;Initial Catalog=myDatabase")
CreateReport("/My Reports", _
"MyReport", _
"C:\myreport.rdl", _
True, _
"/Data Sources", _
"myDataSource")
End Sub
Public Sub CreateFolder(parent As String, name As String)
Dim fullpath As String = GetFullPath(parent, name)
Try
RS.CreateFolder(name, parent, GetCommonProperties())
Console.WriteLine("Folder created: {0}", name)
Catch e As SoapException
If e.Detail.Item("ErrorCode").InnerText = "rsItemAlreadyExists" Then
Console.WriteLine("Folder {0} already exists and cannot be overwritten", fullpath)
Else
Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
End If
End Try
End Sub
Public Sub CreateDataSource(parent As String, name As String, connectionString As String)
Try
RS.CreateDataSource(name, parent,False, GetDataSourceDefinition(connectionString), GetCommonProperties())
Console.WriteLine("DataSource {0} created successfully", name)
Catch e As SoapException
Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
End Try
End Sub
Public Sub CreateReport(parent As String, name As String, location As String, overwrite As Boolean, dataSourcePath As String, dataSourceName As String)
Dim reportContents As Byte() = Nothing
Dim warnings As Warning() = Nothing
Dim fullpath As String = GetFullPath(parent, name)
'Read RDL definition from disk
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, GetCommonProperties())
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", name)
End If
'Set report DataSource references
Dim dataSources(0) As DataSource
Dim dsr0 As New DataSourceReference
dsr0.Reference = dataSourcePath
Dim ds0 As New DataSource
ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
ds0.Name=dataSourceName
dataSources(0) = ds0
RS.SetItemDataSources(fullpath, dataSources)
Console.Writeline("Report DataSources set successfully")
Catch e As IOException
Console.WriteLine(e.Message)
Catch e As SoapException
Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
End Try
End Sub
Public Function GetCommonProperties() As [Property]()
'Common CatalogItem properties
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
Return props
End Function
Public Function GetDataSourceDefinition(connectionString as String)
Dim definition As New DataSourceDefinition
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Prompt = "Enter a user name and password to access the data source:"
definition.WindowsCredentials = False
definition.OriginalConnectStringExpressionBased = False
definition.UseOriginalConnectString = False
Return definition
End Function
Private Function GetFullPath(parent As String, name As String) As String
If parent = "/" Then
Return parent + name
Else
Return parent + "/" + name
End If
End Function
