Reporting Services Tip #3 - rs.exe, backslash problem
In a previous blog, I wrote how you can deploy your reports via Report Manager. It was mentioned in the comments section that "you can use the web service to upload the RDL directory from code". Recently, I started to look into this so that deploying reports would be easier, that is without the deploy option in Visual Studio.
Reporting Services comes with a command line utility, rs.exe, that allows you 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).
You 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, you 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 you 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