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

 

Comments (23)

  1. Tyler says:

    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

  2. Paolo says:

    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.

  3. 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.

  4. 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…

  5. Amar says:

    John,I am also in need of same script what kiran need.Could you please help with me..

  6. I'll see what I can do when I get some time in late June or early July.

  7. Francois says:

    Did you have the time check the functionality to associate reports with shared datasets ?

  8. Vaishali Soneta says:

    I am trying the above code to publish the reports in Sql2008 R2 and it's giving me an error:

    Reading Files from Report Services Project

    Attempting to Deploy Report Name OrderSurveyHostAdvReport

    System.Web.Services.Protocols.SoapException: The report definition is not valid

    or supported by this version of Reporting Services. This could be the result of

    publishing a report definition of a later version of Reporting Services, or tha

    the report definition contains XML that is not well-formed or the XML is not v

    lid based on the Report Definition schema. Details: '.', hexadecimal value 0x00

    is an invalid character. Line 2004, position 10.

      at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.CreateRepo

    t(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[

    Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

      at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.CreateCata

    ogItem(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] D

    finition, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

      at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCatalogI

    em(String ItemType, String Name, String Parent, Boolean Overwrite, Byte[] Defin

    tion, Property[] Properties, CatalogItem& ItemInfo, Warning[]& Warnings)

    Shared data source reference set for reports in the OrderSurvey folder.

    So my datasets r published fine but not my report.. Please help…

  9. Chris Fraelic says:

    Vaishali:

    Look for the line:

    definition = New [Byte](stream.Length) {}

    And change it to:

    definition = New [Byte](stream.Length -1) {}

    Has anyone attempted the shared datasets yet ?

  10. Nishar says:

    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

    http://www.sqlblogspot.com/…/ssrs-deploymentcomplete-automation2012.html

  11. Swati says:

    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

  12. Hi Swati.

    This script is based on SSRS 2008 R2 and uses features that are not available in SSSRS 2005.

  13. Jiken Dubal says:

    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.

  14. Hitesh Gupta says:

    Where and how in this code are we setting the reference for reports to use shared dataset we created?

  15. Arcus says:

    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&quot;

           rs.PreAuthenticate = True

           rs.Credentials = System.Net.CredentialCache.DefaultCredentials

  16. Dharamvir Singh says:

    Does someone has  functionality to associate reports with shared datasets ?

  17. I'm working on that and will post an update as soon as I have a working sample.

  18. Knut says:

    I have written a script that associates reports with shared datasets and datasources.

  19. Dharamvir Singh says:

    Knut, can you please share the script.

  20. srikanth manda says:

    Knut, can you please share the script that associates reports with shared datasets and datasources.

  21. Veena says:

    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.

  22. Andy says:

    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

  23. atul says:

    hi john

    I used your script but stuck in shared dataset issue

Skip to main content