SSRS: Automating Report Deployment using RS.EXE

I was looking for a way to deploy reports on the production server without going through the Report Manager and deploying one report at a time.  Then I stumbled upon RS.EXE, a scripting hosting utility that uses VB.NET script.  With VB.NET script you can deploy all your reports.

But how do you deploy SSRS reports in VB.NET?  Instead of creating one from scratch I searched the Internet and found one from John Desch’s blog Using the RS.EXE utility to deploy a Report Server Project and Shared Dataset.  It was said to be one of the best automated script available but one of the problems with the script is it does not support 2008SP1 which I need it to.

Good thing I found another script based on John Desch’s script but with enhancements including support for 2008SP1.  I found the script from Nishar’s blog SSRS Deployment–Complete Automation–2012 & 2008

I slightly modified the Commonscript.rss file for my own purposes.

'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(DataSourceFolderName, "/", "", "Visible")

'Create the folder that will contain the shared data sets

'CreateFolders(DataSetFolderName, "/", "", "Visible")

'Create the folder that will contain the deployed reports

CreateFolders(ReportFolderName, "/", "", "Visible")

Catch goof As Exception

Console.WriteLine(goof.Message)

End Try

ReadFiles(ReportSourcePath, "*.rds")

'ReadFiles(ReportSourcePath, "*.rsd")

ReadFiles(ReportSourcePath, "*.rdl")

'Publish the report

'PublishReport(ReportName)

'UpdateDataSources(ReportFolderName, 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(ReportSourcePath + "\" + 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, "/" + DataSetFolderName, True, 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("/" + DataSetFolderName + "/" + 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("/" + DataSetFolderName + "/" + 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,DataSetFolderName, DataSourceFolderName,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(ReportSourcePath + "\" + filename)

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

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, "/" + DataSourceFolderName, 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(ReportSourcePath + "\" + reportName)

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

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)

Dim item As CatalogItem


Try

item = rs.CreateCatalogItem("Report", reportname, "/" + ReportFolderName, True, definition, Nothing, warnings)

'warnings = rs.CreateCatalogItem(reportName, "/" + ReportFolderName, False, definition, Nothing)

If Not (warnings Is Nothing) Then

If item.Name <> "" Then

Console.WriteLine("Report: {0} published successfully with warnings", reportName)
UpdateDataSources_report(reportName)
UpdateDataSet_report(reportName)
Else

Dim warning As Warning

For Each warning In warnings

Console.WriteLine(warning.Message)

Next warning

End If

Else

Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
UpdateDataSources_report(reportName)
UpdateDataSet_report(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(ReportFolderName As String, DataSourcePath As String)

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item As CatalogItem

Dim items As CatalogItem()

Try



items = rs.ListChildren("/" + ReportFolderName, False)

For Each item In items

'Console.WriteLine(" update date source called --------"+ item.Path + " -----------")

If item.path.Indexof("rdl") > 0 And ReportName = "" Then

'Console.WriteLine(" update date source called --------"+ item.path.Indexof("rdl").tostring() + " -----------")

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")

'end if
Next

End If

Next

If ReportName = "" Then

Console.WriteLine("Shared data source reference set for reports in the {0} folder.", ReportFolderName)

End If


If ReportName <> "" Then

' Console.WriteLine(" " + "/" + ReportFolderName + "/" + ReportName + " ------------- second update called ---------------------- ")

Dim dataSources() As DataSource = rs.GetItemDataSources("/" + ReportFolderName + "/" + ReportName)

For Each ds As DataSource In dataSources

Dim sharedDs(0) As DataSource

sharedDs(0) = GetDataSource(DataSourcePath, ds.Name)

rs.SetItemDataSources("/" + ReportFolderName + "/" + ReportName, sharedDs)

Console.WriteLine("Set " & ds.Name & " datasource for " & "/" + ReportFolderName + "/" + ReportName & " report")

'end if
Next

Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolderName + "/" + ReportName)

End If

Catch goof As SoapException

Console.WriteLine(goof.Detail.InnerXml.ToString())

End Try

End Sub




'Utility to Update The Data Sources on the Server

Public Sub UpdateDataSources_report(ReportName As String)

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim item As CatalogItem

Dim items As CatalogItem()

Try

'If ReportName <> "" then

' Console.WriteLine(" " + "/" + ReportFolderName + "/" + ReportName + " ------------- second update called ---------------------- ")

Dim dataSources() As DataSource = rs.GetItemDataSources("/" + ReportFolderName + "/" + ReportName)

For Each ds As DataSource In dataSources

Dim sharedDs(0) As DataSource

sharedDs(0) = GetDataSource(DataSourcePath, ds.Name)

rs.SetItemDataSources("/" + ReportFolderName + "/" + ReportName, sharedDs)

Console.WriteLine("Set " & ds.Name & " datasource for " & "/" + ReportFolderName + "/" + ReportName & " report")

'end if
Next

Console.WriteLine("All the shared data source reference set for report {0} ", "/" + ReportFolderName + "/" + ReportName)

'end if


Catch goof As SoapException

Console.WriteLine(goof.Detail.InnerXml.ToString())

End Try

End Sub



'Utility to link The Dataset with the Report

Public Sub UpdateDataSet_report(ReportName As String)

rs.Credentials = System.Net.CredentialCache.DefaultCredentials


Try

Dim dataSets As ItemReferenceData() = rs.GetItemReferences("/" + ReportFolderName + "/" + ReportName, "DataSet")

If dataSets IsNot Nothing AndAlso dataSets.Length > 0 AndAlso Not String.IsNullOrEmpty(dataSets(0).Name) Then

For i As Integer = 0 To dataSets.Length - 1

Dim references(0) As ItemReference
Dim sharedDataSet = New ItemReference()
sharedDataSet.Name = dataSets(i).Name
Console.WriteLine("Attempting to Link Dataset {0}", dataSets(i).Name)
sharedDataSet.Reference = "/" + DataSetFolderName + "/" + dataSets(i).Name
references(0) = sharedDataSet
rs.SetItemReferences("/" + ReportFolderName + "/" + ReportName, references)
Console.WriteLine("Report " + ReportName + " Linked to data set " + "/" + DataSetFolderName + "/" + Convert.ToString(sharedDataSet.Name))
Next

End If

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 Link Data Source {0}", ds.Name)

GetDataSource = ds

End Function

 

I also modified the deploy.bat file as well.

set ServerPath=http://localhost/ReportServer
set DataSourceFolderName=Data Sources
set DataSourcePath=Data Sources
set DataSetFolderName=Datasets
set ReportFolderName=My Reports
set ReportSourcePath=.\My Reports
set ReportName=

rs.exe
-i Commonscript.rss
-s %ServerPath%
-v DataSourceFolderName="%DataSourceFolderName%"
-v DataSourcePath="%DataSourcePath%"
-v DataSetFolderName="%DataSetFolderName%"
-v ReportFolderName="%ReportFolderName%"
-v ReportSourcePath="%ReportSourcePath%"
-v ReportName="%ReportName%"
-e Mgmt2010

Advertisements

Demonstrating the new HTML5 and CSS3 Features

Now that I have a public website with my own domain name, I decided to publish by web pages that demonstrate the new HTML5 and CSS3 features.  I will regularly update this post with links to my web pages.

HTML5:

  • Form Input Types
  • Canvas (and WebGL)
  • SVG
  • Video and Audio
  • Geolocation
  • Drag/Drop
  • Web Storage
  • Application Cache
  • Web Workers
  • SSE (and WebSockets)

CSS3:

  • Borders, Backgrounds, Gradients, Text Effects, and Fonts (Web Fonts)
  • 2D and 3D Transforms
  • Transitions
  • Animations
  • Multiple Columns
  • Flexi Box Model
  • User Interface

Access: Type Mismatch Run-time Error After Converting to ACCDB Format

You might receive a Run-time error ’13’: Type mismatch after converting a Microsoft Access database from .mdb to .accdb format.  The solution might be as easy as removing the reference to the ADO object library, if you are not using it.

  • Open the Access database in question.
  • Open the Visual Basic Editor (VBE) by pressing ALT+F11.
  • On the Tools menu, click References.
  • In the References dialog box, uncheck Microsoft ActiveX Data Objects, and click OK.