Reporting Services comes with a command line utility, rs.exe, that allows us to administer Report Server via scripts that run Web Service operations.  These scripts must be written in VB.NET and a suggested extension for them is rss (Reporting Services script file).

For more information, see http://msdn.microsoft.com/en-us/library/ms162839.aspx

http://msdn.microsoft.com/en-us/library/ms159720.aspx

We can use rs.exe to publish the sample reports provided by MS.  Information about this can be found in SQL Server Reporting Services Books Online.  Reading this information helped me write a script that publishes my reports.  The problem with their script is that it assumes that the reports do not already exist on that Report Server.  My script, however, overwrites them if they exist.  I also didn’t want to hard code the database server name, which is what their script does, so I used a variable in the script instead and will get the value from the rs.exe command.  The rss code is at the end of this weblog.

To call the rss script via rs.exe, we specify the file name, URL of Report Server, and the input parameters.  This rss script has 3 inputs.  One for the SQL Server name, one for the name of the parent folder, and the other for the path to the rdl files.  One thing to note is that SQL Server Reporting Services Books Online shows how to pass in the input parameters.  But the documentation is incorrect, which I found out from MS via a support case that I opened for a different problem which I’ll discuss in a bit.  The documentation says to do this -v a=”b” c=”d”.  This should be corrected to -v a=”b” -v c=”d”.  So we must specify the v switch for each input parameter.

Here’s an example call:

rs -i PublishReports.rss -s http://ServerName/reportserver 
-v filePath="C:\Reports" 
-v sqlServerName="SQLServerName" 
-v parentFolder="SomeParentFolder"

PublishReports.rss is the name of the script file that I used.  If you don’t want to navigate to it via the command line, then you can include its path as well:

rs -i C:\SomeLocation\PublishReports.rss -s http://ServerName/reportserver 
-v filePath="C:\Reports" 
-v sqlServerName="SQLServerName" 
-v parentFolder="SomeParentFolder"

The support case that I opened with MS had to do with me trying to call rs.exe with 3 input parameters like this:

rs -i PublishReports.rss -s http://ServerName/reportserver 
-v filePath="C:\Reports\" 
-v sqlServerName="SQLServerName" -v parentFolder="SomeParentFolder"

The difference between this last one and the others is the ending backslash in the filePath input parameter.  When you try running it, it will give you an error.  It turns out that when you have more than 2 input parameters, you must escape this backslash in the rss code.  Obviously this is a bug, but I’m not sure if this will be included in the next service pack or not.  In the code that I have below, I have included the backslash to avoid this bug.

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder

Public Sub Main()

 rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 Dim name As String

 'Create the parent folder
 Try
  rs.CreateFolder(parentFolder, "/", Nothing)
  Console.WriteLine("Parent folder created: {0}", parentFolder)

 Catch e As Exception
  'Console.WriteLine(e.Message)

 End Try

 CreateHOSDataSource(sqlServerName)

 PublishReport("Report1")
 PublishReport("Report2")
 PublishReport("Report3")
 PublishReport("Report4")
 PublishReport("Report5")

End Sub

Public Sub CreateHOSDataSource(ByVal sqlServerName As String)

 Dim name As String = "DataSource1"
 Dim parent As String = "/" + parentFolder

 'Define the data source definition.
 Dim definition As New DataSourceDefinition()
 definition.CredentialRetrieval = CredentialRetrievalEnum.Store
 definition.ConnectString = "data source=" & sqlServerName & 
                    ";initial catalog=DB1"
 definition.Enabled = True
 definition.EnabledSpecified = True
 definition.Extension = "SQL"
 definition.ImpersonateUser = False
 definition.ImpersonateUserSpecified = True
 definition.WindowsCredentials = False
 definition.UserName = "SomeUser"
 definition.Password = "somepassword"

 Try
  rs.CreateDataSource(name, parent, True, definition, Nothing)

 Catch e As Exception
  Console.WriteLine(e.Message)

 End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)

 Try
  Dim stream As FileStream = File.OpenRead(filePath + 
           "\" + reportName + ".rdl")
  definition = New [Byte](stream.Length) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()

    Catch e As IOException
        Console.WriteLine(e.Message)

    End Try

    Try
        warnings = rs.CreateReport(reportName, parentPath, 
               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", reportName)

        End If

    Catch e As Exception
        Console.WriteLine(e.Message)

    End Try

End Sub