WTF Next?

Dev ramblings from a master of nothing.

  Home  |   Contact  |   Syndication    |   Login
  136 Posts | 0 Stories | 109 Comments | 0 Trackbacks

News

INETA Community Speakers Program
GeeksWithBlogs.net: WTFNext's hosting!

View Stacy Vicknair's profile on LinkedIn

Twitter







Tag Cloud


Archives

Post Categories

Community Links

User Groups

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.

SNAGHTML7ccebfc

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
posted on Saturday, March 31, 2012 12:19 PM