Share via

Using the RS.EXE utility to deploy a Report Server Project and Shared Dataset.

  • Article

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

Comments

  • Anonymous
    December 26, 2012
    Hello, this approach heavily leverages the format of the rdl, rsd, rds XML files - is this to say the API doesn't support particular functionality (eg. assigning a Data Set a Data Source, when you have to determine the Data Source name - thinking this would be a call to GetItemReferences/SetItemReferences).  If at all possible surely the API approach is preferred

  • Anonymous
    January 27, 2013
    I have tried your script, for the most part it works great. However, I have a report with a shared dataset. Your script does not associate the report to the dataset.

  • Anonymous
    January 31, 2013
    The script is a work in progress, and I'm planning on adding functionality to associate reports with shared datasets when I get some free time.

  • Anonymous
    April 18, 2013
    Hi John, I am in need of your script very badly which is written above.But I need Shared data source implementation also.It would be great if you spend some time and help me with the script for setting the RDLs to Shared datasets.And also it would be great if you add code for mapping the RDL directly to the Datasource. Thanks in Advance...

  • Anonymous
    April 21, 2013
    John,I am also in need of same script what kiran need.Could you please help with me..

  • Anonymous
    May 13, 2013
    I'll see what I can do when I get some time in late June or early July.

  • Anonymous
    October 23, 2013
    Did you have the time check the functionality to associate reports with shared datasets ?

  • Anonymous
    November 29, 2013
    The comment has been removed

  • Anonymous
    December 18, 2013
    The comment has been removed

  • Anonymous
    March 08, 2014
    Many Thanks John, I was trying to automate the Deployment for quite some time and this is an excellent code base. I have added few more functionality and automated with batch file. Please check here www.sqlblogspot.com/.../ssrs-deploymentcomplete-automation2012.html

  • Anonymous
    April 15, 2014
    The script does not compile, The specified script failed to compile with the following errors: error BC30456: 'CreateCatalogItem' is not a member of 'Microsoft.SqlServer.ReportingServices2005.ReportingService2005'. and more such errors

  • Anonymous
    April 18, 2014
    Hi Swati. This script is based on SSRS 2008 R2 and uses features that are not available in SSSRS 2005.

  • Anonymous
    June 26, 2014
    Thank you for put rss code here. rs.exe -i myScriptFile.rss -s http://myServer/reportserver -u  [domain]username -p  password how to use username and password in rss file? I am not able to found on MSDN. I found below code lines from web, but how to use rs arguments in below code lines in rss file. rs.Credentials = System.Net.CredentialCache.DefaultCredentials rs.Credentials = New System.Net.NetworkCredential(username, password, domain) I am waiting for it.

  • Anonymous
    August 25, 2014
    Where and how in this code are we setting the reference for reports to use shared dataset we created?

  • Anonymous
    August 28, 2014
    Hi Jiken, you should include the rs.credentials Definition into your .rss file, really. Whenever a sub calls the rs Webservice, credentials must be passed for user authenfication. Pls find below a snippet for Reportserver Webservice SQL Server 2008 and later:        rs.Url = "http://han-ax-bid/ReportServer/ReportService2010.asmx"        rs.PreAuthenticate = True        rs.Credentials = System.Net.CredentialCache.DefaultCredentials

  • Anonymous
    January 01, 2015
    Does someone has  functionality to associate reports with shared datasets ?

  • Anonymous
    January 30, 2015
    I'm working on that and will post an update as soon as I have a working sample.

  • Anonymous
    January 31, 2015
    I have written a script that associates reports with shared datasets and datasources.

  • Anonymous
    April 12, 2015
    Knut, can you please share the script.

  • Anonymous
    July 15, 2015
    Knut, can you please share the script that associates reports with shared datasets and datasources.

  • Anonymous
    October 05, 2015
    Did anyone got the association of shared Data source & dataset working, I am looking for it for a while now. Please share if anyone got it to work.

  • Anonymous
    November 10, 2015
    The link posted by Nishar on 9 Mar 2014 has code for updating the shared data source and data set. Here's a copy of some code from my working script. Note that this is for sharepoint integrated reporting services, ymmv if using native mode. Left out is the code that publishes the report, which saves the report object as "item". DataSourcePath and DataSetPath are set to the full url of the folder containing sata sources and shared data sets, respectively: ' Update data reference Dim dataSources() As DataSource = rs.GetItemDataSources(item.Path) For Each ds As DataSource In dataSources Try Console.WriteLine("Updating DataSource {0}", ds.Name) Dim sharedDs(0) As DataSource sharedDs(0) = GetDataSource(DataSourcePath, ds.Name, DataSourceName) rs.SetItemDataSources(item.Path, sharedDs) Console.WriteLine("Set " & ds.Name & " datasource for " & item.Path & " report") Catch E As Exception Console.WriteLine(E.Message) End Try Next ' Update shared data sets Dim dataSets() As ItemReferenceData = rs.GetItemReferences(item.Path, "DataSet") For Each ds as ItemReferenceData In dataSets Try Dim references(0) As ItemReference Dim sharedDataSet As New ItemReference() sharedDataSet.Name = ds.Name Console.WriteLine("Attempting to Link Dataset {0}", ds.Name) sharedDataSet.Reference = DataSetPath + "/" + ds.Name + ".rsd" references(0) = sharedDataSet rs.SetItemReferences(item.Path, references) Console.WriteLine("Report " + item.Path + " Linked to data set " + DataSetPath + "/" + Convert.ToString(sharedDataSet.Name)) Catch E As Exception Console.WriteLine(E.Message) End Try Next

  • Anonymous
    December 16, 2015
    hi john I used your script but stuck in shared dataset issue