Reporting Services uses named data sources that are evaluated at run time. You can leverage this by creating a consistently named data source on each Reporting Services server that points to the SQL Server instance that holds your data. The same concept applies if your data is spread over instances. This process can be automated too, but as it only happens once per Report Server instance, I did it manually.
If, as is common, your Reporting Services server is also your SQL Server you can just have all the data sources use (local).
What changes with each new release is the datasets, the reports, and the report parts.
Devenv
If you are pushing from an environment that has Visual Studio, you can simply run devenv to deploy the report project. DevEnv can be found in Program Files\Miscrosoft Visual Studio\<version>\Common7\IDE. The following example deploys all the reports in the PurchasingReports solution using the release configuration and creates a log file in Deployment.log.devenv.exe PurchasingReports.sln /deploy release /out Deployment.log
You could create a different configuration for each of your Reporting Services servers and change the /deploy option to deploy to different servers. For example you could have Debug, Release, California, Ukraine, and Taiwan configurations.
But often deployment is done by pulling from the target server. This makes starting and stopping services and other scripting tasks easier. It's unlikely you will have Visual Studio installed on a production server so this devenv approach won't work.
RS.exe
RS.exe is Microsoft's weird way of allowing you to write a VB.Net script that is compiled and executed at run time in an environment that has a specific reporting services SOAP library included. The documentation says you can create the script in Visual Studio but you get no Intellisense or any other features so you may as well just write the script in Notepad. The only important thing is that the script file must have the rss extension.
Let's write a script that pulls reports and data sets from a build server, overwriting any existing copies. You can also pull data sources, security, subscriptions and a host of other things. In addition, you could do a lot of other, non-reporting, things if you wanted.
We will need a batch file to run our script. RS.exe can be found in Program Files\Microsoft SQL Server\120\Tools\Binn. I'm going to target SQL Server 2010 (see the -e option). The batch file looks like this...
rs.exe -i "rsDeploy.rss" -s (local) -e Mgmt2010
-i specifies the path to the rss script
-s specifies the name of the target Reporting Service instance
-e specifies the SOAP endpoint to target. The default is Mgmt2005.
There are other options, but they are not relevant to this post.
Now we have to write rsDeploy.rss. We could do it in Visual Studio or we could do it in Notepad. Remember it's just a console app.
We start by declaring Main(), establishing credentials, and making sure our dataset and report folders exist, Note there is an implied object called rs which is an instance of ReportingService2010 (because of the endpoint we chose). We will make heavy use of this object so it's worth looking at the documentation on MSDN. This example copies reports for my purchasing project. Substitute your own name for <build server>.
Dim reportFolder As String = "Purchasing"
Dim reportPath as string = "/" & reportFolder
Dim reportFilePath As String = "\\<build server>\PurchasingReports"
Dim reportFileName As String = "*.rdl"
Dim datasetFolder As String = "DataSets"
Dim datasetPath as string = "/" & datasetFolder
Dim datasetFilePath As String = "\\<build server>\PurchasingReports"
Dim datasetFileName As String = "*.rsd"
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' Create the parent folder
Try
rs.CreateFolder(reportFolder, "/", Nothing)
Console.WriteLine("Parent Folder {0} created successfully", reportFolder)
rs.CreateFolder(datasetFolder, "/", Nothing)
Console.WriteLine("Parent Folder {0} created successfully", datasetFolder)
Catch ex as Exception
Console.WriteLine("Creating Parent Folder {0}:{1}", reportFolder, ex.Message)
End Try
End Sub
Next we will loop through our datasets and deploy them one by one. Add the following code to the end of Main.
' Publish the datasets
Dim datasets as string() = System.IO.Directory.GetFiles(datasetFilePath, datasetFileName)
For Each dataset as string in datasets
PublishDataSet(dataset)
Next
The PublishDataSet method is quite simple. A lot of information is returned in the warnings collection and in the CI object.
Public Sub PublishDataSet(dataset as string)
Dim stream As FileStream
Dim datasetName As String
Dim CI as CatalogItem
Try
Console.Write("Publishing dataset {0}:", dataset)
stream = File.OpenRead(dataset)
definition = New [Byte](stream.length - 1){}
stream.Read(definition, 0, CInt(stream.length))
stream.Close()
datasetName = System.IO.Path.GetFileName(dataset).replace(".rsd","")
CI = rs.CreateCatalogItem("DataSet", datasetName, datasetPath, true, definition, Nothing, warnings)
Console.WriteLine("OK")
Catch ex as Exception
Console.WriteLine(ex.Message)
End Try
End Sub
Publish reports is almost identical. You can see how you could extend this to deploy other reporting services features. Add the following to Main...
' Publish the reports
Dim reports as String() = System.IO.Directory.GetFiles(reportFilePath, reportFileName)
For Each report as String in reports
PublishReport(report)
Next
and add this method too. Note that after deploying the report we need to link it to the consistently named data source I mentioned at the top of the article.
Public Sub PublishReport(report as String)
Dim stream As FileStream
Dim reportName As String
Dim CI as CatalogItem
Try
Console.Write("Publishing {0}:", report)
stream = File.OpenRead(report)
definition = New [Byte](stream.length - 1){}
stream.Read(definition, 0, CInt(stream.length))
stream.Close()
reportName = System.IO.Path.GetFileName(report).replace(".rdl","")
CI = rs.CreateCatalogItem("Report", reportName, reportPath, true, definition, Nothing, warnings)
' Set the DataSource
Dim ds(0) as DataSource
Dim s as new DataSource
Dim dsr As New DataSourceReference
dsr.Reference = "/Data Sources/Purchasing"
s.Item = dsr
s.Name="Purchasing"
ds(0) = s
Dim myItem As String = reportPath & "/" & reportName
rs.SetItemDataSources(myItem, ds)
Console.WriteLine("OK")
Catch ex as Exception
Console.WriteLine(ex.Message)
End Try
End Sub
Now all we have to do is run the batch file, fix all the build errors and we're good to go.
