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