Using the RS.EXE utility to deploy a Report Server Project and Shared Dataset.
Since broadening my area of concentration and branching out from Microsoft Analysis Services to the broader Microsoft Business Intelligence Stack, I've encountered quite a few new challenges and opportuities. One of the more recent challenges was how to deploy a Reporting Services project. When using BI Development Studio or, in the case of the SQL Server 2012 tool set, SQL Server Data Tools deployment is a relatively trivial matter. This deployment task, however, involved programmatic deployment of Data Sources, Reports, and Shared DataSets. It didn't take long to discover that Reporting Services ships the RS script hosting utility, which can be used for any number of administrative tasks related to a Reporting Services implementation.
The RS utility is a script hosting utility that uses VB.NET script, so if you're familiar with VB.NET and scripting, it's relatively easy to create .rss files to perform just about any task that you might want to perform. There are a number of sample scripts that are available in Books On Line and there are several examples of .rss scripts available on the web. David Elish has posted a useful .rss script for automation of some tasks. Jasper Smith has made the Reporting Services Scripter utility available. Armed with that knowledge and a false sense of security, I thought this should be easy.
Just one minor problem. Shared Datasets were new with the 2008 R2 release and there were no updated samples that shipped with that release. The familiar Reporting Services 2005 and Reporting Services 2008 classes were there, but they didn't support Shared DataSets or deployment to a Reporting Services server configured for SharePoint integrated mode. Fortunately, there was a new Reporting Services 2010 class that introduced a CreateCatalogItem method and enabled the RS utility to be used with Native mode as well as SharePoint Integrated Mode. Coincidentally, the CreateCatalogItem method can be used with Reports, DataSources, and DataSets, so after a bit of reading I was ready to code.
Note that the RS utility is run from a command prompt, and on Windows Server 2008 R2 or later, it needs to be run from a command prompt with elevated permissions. In order to run the tool, you must have permissions to connect to the report server instance against which the script will be run. What follows is the code for an RS script that can be used to deploy a Reporting Services Project, including Data Sources, Shared DataSets, and Reports, to a Report Server. Hopefully you'll find it useful.
The command line for running this script is:
rs.exe -i <path_to_script> -s <url_of_report_server> -v ReportFolder="<report_folder_name>" -v DataSetFolder="<DataSet_Folder_Name>" -v DataSourceFolder="<DataSource_Folder_Name>" -v DataSourcePath="/<DataSource_Path>" -v filePath="<Path_To_Report_Server_Project_Files>" -eMgmt2010
'Begin Script
Dim definition As [Byte]() = Nothing
Dim bytedefinition as [Byte]() = nothing
Dim warnings As Warning() = Nothing
'Main Entry point of utility
Public Sub Main()
Console.WriteLine()
Console.WriteLine("Initiating Deployment")
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Try
'Create the shared data source
CreateFolders(DataSourceFolder,"/","Data Sources","Visible")
'Create the folder that will contain the shared data sets
CreateFolders(DataSetFolder, "/", "Data Set Folder", "Visible")
'Create the folder that will contain the deployed reports
CreateFolders(ReportFolder, "/", "Report Folder","Visible")
Catch goof As Exception
Console.WriteLine(goof.Message)
End Try
ReadFiles(filepath, "*.rds")
ReadFiles(filepath, "*.rsd")
ReadFiles(filepath, "*.rdl")
'Publish the report
'PublishReport(ReportName)
UpdateDataSources(ReportFolder, DataSourcePath)
End Sub
'Utility for creation of folders
Public Sub CreateFolders(ByVal folderName as string, ByVal parentPath as string, ByVal description as String, ByVal visible as string)
Console.WriteLine()
Console.WriteLine("Checking for Target Folders")
'CatalogItem properties
Dim descriptionProp as new [Property]
descriptionProp.Name = "Description"
descriptionProp.Value= description
Dim visibleProp as new [Property]
visibleProp.Name = "Visible"
visibleProp.value= visible
Dim props(1) as [Property]
props(0) = descriptionProp
props(1) = visibleProp
Try
rs.CreateFolder(folderName,parentPath,props)
Console.WriteLine("Folder {0} successfully created", foldername)
Catch goof as SoapException
If goof.Message.Indexof("AlreadyExists")>0 Then
Console.WriteLine("Folder {0} already exists",foldername)
End If
End Try
End Sub
'Utility for reading files from the Report Sevices Project
Public sub ReadFiles(filepath as string, fileextension as string)
Console.WriteLine()
Console.WriteLine("Reading Files from Report Services Project")
Dim rptdirinfo As System.IO.DirectoryInfo
rptdirinfo = New System.IO.DirectoryInfo(filepath)
Dim filedoc As FileInfo()
filedoc = rptdirinfo.GetFiles(fileextension)
Try
For rptcount As Integer = 0 To filedoc.Length-1
If Not filedoc(rptcount).Name.ToString.Trim.ToUpper.Contains("BACKUP") Then
SELECT Case fileextension
Case "*.rds"
CreateDataSource(filedoc(rptcount).tostring.trim)
Case "*.rsd"
CreateDataSet(filedoc(rptcount).tostring.trim)
Case "*.rdl"
PublishReport(filedoc(rptcount).tostring.trim)
End Select
End If
Next
Catch goof as Exception
Console.WriteLine("In ReadFiles " + goof.message)
End Try
End Sub
'Utility for Creating Shared Data Sets contained in the project
Public Sub CreateDataSet(ByVal filename as string)
Dim valstart as integer
Dim valend as integer
Dim DSDefinitionStr as string
Dim DataSourceName as string
Dim QueryString as string
Try
Dim stream As FileStream = File.OpenRead(filePath + "\" + filename )
definition = New [Byte](stream.Length-1) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
For i As Integer = 0 To definition.Length - 1
DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(definition(i).ToString)))
Next
valstart=DSDefinitionStr.ToString.Indexof("<DataSourceReference>")
If valstart > 0 Then
valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceReference>") + 21
valend = DSDefinitionStr.ToString.IndexOf("</DataSourceReference>")
DataSourceName=DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
Console.WriteLine(DataSourceName)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
filename=filename.tostring.replace(".rsd","")
Console.WriteLine("Attempting to Deploy DataSet {0}", filename)
Try
Dim item as CatalogItem
item=rs.CreateCatalogItem("DataSet",filename, "/" + DataSetFolder, false, definition, nothing, warnings)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
if warning.message.tostring.tolower.contains("refers to the shared data source") then
Console.WriteLine("Connecting DataSet {0} to Data Source {1}",filename, DataSourceName)
Dim referenceData() as ItemReferenceData = rs.GetItemReferences("/" + DataSetFolder + "/" + filename,"DataSet")
Dim references(0) as ItemReference
Dim reference as New ItemReference()
Dim datasourceURL = DataSourcePath + "/" + DataSourceName
reference.name=referenceData(0).Name
Console.WriteLine("Reference name = " + reference.name)
reference.Reference=datasourceURL
references(0)=reference
rs.SetItemReferences("/" + DataSetFolder + "/" + filename, references)
else
Console.WriteLine(warning.Message)
end if
Next warning
Else
Console.WriteLine("DataSet: {0} published successfully with no warnings", filename)
End If
Catch goof as SoapException
If goof.Message.Indexof("AlreadyExists")>0 Then
Console.WriteLine("The DataSet {0} already exists",fileName.ToString)
Else
If goof.Message.IndexOf("published")=-1 Then
Console.Writeline(goof.Message)
End If
End If
End Try
'UpdateDataSetSources(filename,DataSetFolder, DataSourceFolder,DataSourceName)
End Sub
'Utility for creating Data Sources on the Server
Public Sub CreateDataSource(filename as string)
'Define the data source definition.
Dim dsDefinition As New DataSourceDefinition()
Dim DataSourceName as string
Dim valstart As Integer
Dim valend As Integer
Dim ConnectionString As String
Dim Extension As String
Dim IntegratedSec As String
Dim DataSourceID As String
Dim PromptStr As String
PromptStr=""
Dim DSDefinitionStr As String
DSDefinitionStr = ""
DataSourceName=filename.tostring.trim.substring(0,filename.tostring.trim.length-4)
Console.WriteLine("Attempting to Deploy Data Source {0}", DataSourceName)
Try
Dim stream As FileStream = File.OpenRead(filepath + "\" + filename)
bytedefinition = New [Byte](stream.Length) {}
stream.Read(bytedefinition, 0, CInt(stream.Length))
stream.Close()
For i As Integer = 0 To bytedefinition.Length - 1
DSDefinitionStr = DSDefinitionStr + Convert.ToString(Convert.ToChar(Convert.ToInt16(bytedefinition(i).ToString)))
Next
Catch goof As IOException
Console.WriteLine(goof.Message)
End Try
If DSDefinitionStr.ToString.Contains("<ConnectString>") And DSDefinitionStr.ToString.Contains("</ConnectString>") Then
valstart = DSDefinitionStr.ToString.IndexOf("<ConnectString>") + 15
valend = DSDefinitionStr.ToString.IndexOf("</ConnectString>")
ConnectionString = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
End If
If DSDefinitionStr.ToString.Contains("<Extension>") And DSDefinitionStr.ToString.Contains("</Extension>") Then
valstart = DSDefinitionStr.ToString.IndexOf("<Extension>") + 11
valend = DSDefinitionStr.ToString.IndexOf("</Extension>")
Extension = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
End If
If DSDefinitionStr.ToString.Contains("<IntegratedSecurity>") And DSDefinitionStr.ToString.Contains("</IntegratedSecurity>") Then
valstart = DSDefinitionStr.ToString.IndexOf("<IntegratedSecurity>") + 20
valend = DSDefinitionStr.ToString.IndexOf("</IntegratedSecurity>")
IntegratedSec = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
End If
If DSDefinitionStr.ToString.Contains("<DataSourceID>") And DSDefinitionStr.ToString.Contains("</DataSourceID>") Then
valstart = DSDefinitionStr.ToString.IndexOf("<DataSourceID>") + 14
valend = DSDefinitionStr.ToString.IndexOf("</DataSourceID>")
DataSourceID = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
End If
If DSDefinitionStr.ToString.Contains("<Prompt>") And DSDefinitionStr.ToString.Contains("</Prompt>") Then
valstart = DSDefinitionStr.ToString.IndexOf("<Prompt>") + 8
valend = DSDefinitionStr.ToString.IndexOf("</Prompt>")
PromptStr = DSDefinitionStr.ToString.Substring(valstart, valend - valstart)
End If
dsdefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
dsdefinition.ConnectString = ConnectionString
dsdefinition.Enabled = True
dsdefinition.EnabledSpecified = True
dsdefinition.Extension = extension
dsdefinition.ImpersonateUser = False
dsdefinition.ImpersonateUserSpecified = True
'Use the default prompt string.
If PromptStr.ToString.Length=0 Then
dsdefinition.Prompt = Nothing
Else
dsdefinition.Prompt = PromptStr
End if
dsdefinition.WindowsCredentials = False
Try
rs.CreateDataSource(DataSourceName, "/" + DataSourceFolder, False, dsdefinition, Nothing)
Console.WriteLine("Data source {0} created successfully", DataSourceName.ToString)
Catch goof as SoapException
If goof.Message.Indexof("AlreadyExists")>0 Then
Console.WriteLine("The Data Source name {0} already exists",DataSourceName.ToString)
End If
End Try
End Sub
'Utility to Publish the Reports
Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + "\" + reportName )
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
reportname=reportname.tostring.replace(".rdl","")
Console.WriteLine("Attempting to Deploy Report Name {0}", reportname.tostring)
Try
Dim item as CatalogItem
item=rs.CreateCatalogItem("Report",reportname, "/" + ReportFolder, false, definition,nothing, warnings)
'warnings = rs.CreateCatalogItem(reportName, "/" + ReportFolder, False, 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 goof as SoapException
If goof.Message.Indexof("AlreadyExists")>0 Then
Console.WriteLine("The Report Name {0} already exists",reportName.ToString)
Else
If goof.Message.IndexOf("published")=-1 Then
Console.WriteLine(goof.Message)
End If
End If
End Try
End Sub
'Utility to Update The Data Sources on the Server
Public Sub UpdateDataSources(ReportFolder as string, DataSourcePath as string)
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim item as CatalogItem
Dim items as CatalogItem()
Try
items=rs.ListChildren("/" + ReportFolder, False)
For Each item in items
Dim dataSources() as DataSource = rs.GetItemDataSources(item.Path)
For Each ds as DataSource in dataSources
Dim sharedDs(0) as DataSource
sharedDs(0)=GetDataSource(DataSourcePath, ds.Name)
rs.SetItemDataSources(item.Path, sharedDs)
Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report")
Next
Next
Console.WriteLine("Shared data source reference set for reports in the {0} folder.", ReportFolder)
Catch goof As SoapException
Console.WriteLine(goof.Detail.InnerXml.ToString())
End Try
End Sub
'Function to Reference Data Sources
Private Function GetDataSource(sharedDataSourcePath as string, dataSourceName as String) as DataSource
Dim reference As New DataSourceReference()
Dim ds As New DataSource
reference.Reference = sharedDataSourcePath & "/" & dataSourceName
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = dataSourceName
Console.WriteLine("Attempting to Publish Data Source {0}", ds.Name)
GetDataSource=ds
End Function