Geeks With Blogs

News INETA Community Speakers Program WTFNext's hosting!

View Stacy Vicknair's profile on LinkedIn

WTF Next? Dev ramblings from a master of nothing.

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 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", _
End Sub
Public Sub CreateFolder(parent As String, name As String)
    Dim fullpath As String = GetFullPath(parent, name)
        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)
            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)
        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
        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, GetCommonProperties())
        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            For Each warning In warnings
            Next warning
            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)
        dataSources(0) = ds0
        RS.SetItemDataSources(fullpath, dataSources)
        Console.Writeline("Report DataSources set successfully")
    Catch e As IOException
    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
        Return parent + "/" + name
    End If
End Function
Posted on Saturday, March 31, 2012 12:19 PM | Back to top

Comments on this post: Automated SSRS deployment with the RS utility

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Stacy Vicknair | Powered by: