Sunday, January 19, 2014


Deploying the reports manually and assigning data source to them is a tiresome process.. I thought there might be a way in which we can reduce the efforts.. That is when i found that there is a utility called RS using which we can reduce the effort.
Let us see how we can automate the report deployment with the help of the utility. Following are the few things that we are going to automate.
1. Create folder if no other folder is available with the same name.
2. Create a Data Source if there is no other database available with the same name.
3. Deploy the reports to the report server.
4. Assign the data source to the deployed report.

Even before we jump into the deployment, Let us understand something about the Utility that is available and where can we find it in our system.
What is RS Utility?
      It is an executable file which processes the script that you provide in an input file. Using which you can automate the reports deployment and administration.

To read more on RS Utility please refer to the following link
Where do you find this Utility?
     It might be present on different location based on the installation.

One common path is “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn“, Please refer to the screen shot mentioned below
Now Let us see what all the files that are required to automate the deployment of reports
1)      Reporting Service Script File (.rss)
2)      Batch File (.bat)

RSS File – This is the important file which contains the script for creating the folder, creating the data source, deploying the reports, assigning the data source to the deployed reports.
Batch File – This is where we call the RSS script file with the help of RS utility. It is not a mandatory file we can use the command prompt to run the RSS Script file. But the batch file will save the effort of the operations team. Once we provide them a batch file with appropriate parameters and the command they can deploy the reports by just running the batch file.
We will see the contents of the RSS Script file. create the individual methods in the first place and then we will call those methods in the Main.
First let us Create the folder in the report server to publish the reports using the following method.
'Create the Folder to deploy the reports and Datasources
Public Sub CreateFolder(ByVal folderName As String, ByVal folderPath As String, ByVal description As String, ByVal hidden As String)
'Common CatalogItem properties
Dim descprop As New [Property]
descprop.Name = "Description"
descprop.Value = description
Dim hiddenprop As New [Property]
hiddenprop.Name = "Hidden"
hiddenprop.Value = hidden
Dim props(1) As [Property]
props(0) = descprop
props(1) = hiddenprop
  'Passing the Folder Name, Path of the Folder and Basic Properties
rs.CreateFolder(folderName, folderPath, props)
Console.WriteLine("Information: Folder " + folderName + " Created Successfully")
Catch e As Exception
End Try
End Sub
The method CreateFolder takes four arguments like folderName, folderPath, description and hidden property. It creates the folder with the name we pass to the argument folderName under the path we specify using the argument folderpath. The arguments description and hidden property are applied to the folder.
Following method is used to create the datasource
'Create Data sources to access the source
Private Sub CreateDataSource (ByVal DataSourcePath As String, ByVal DataSourceName As String, ByVal ServerName As String, ByVal DatabaseName As String)
Dim dsDefinition As New DataSourceDefinition()
dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
dsDefinition.ConnectString = "Data Source="& ServerName &";Initial Catalog=" + DatabaseName
dsDefinition.Enabled = True
dsDefinition.EnabledSpecified = True
dsDefinition.Extension = "SQL"
dsDefinition.ImpersonateUser = False
dsDefinition.ImpersonateUserSpecified = True
dsDefinition.WindowsCredentials = False
rs.CreateDataSource(DataSourceName, DataSourcePath, false, dsDefinition, Nothing)
Catch e As Exception
End Try
End Sub
The method CreateDataSource takes four arguments like DataSourcePath, DataSourceName, ServerName and DatabaseName. It creates the data source with the name we pass to the argument DataSourceName under the path in argument DataSourcePath. The serverName and the DatabaseName will be passed to the connection string to make a connection. It selects the Data Source Type as SQL Server and the credentials as Windows Integrated Security.
Following is the Function to check the availability of the Folders, Reports and data Sources
The function CheckItemAvailability takes three arguments ItemPath, ItemName and ItemType and returns an integer value 0 or 1. Using this method we can check the availability of the folder, reports and datasource by the ItemType value. For checking the report we pass the value “Report” for the argument ItemType. We pass the value “Folder” for the argument ItemType to check the Folder. The value “DataSource” is passed as an ItemType to check the Data Source. When this function returns the value 1 then the item is already available and 0 if the item is not available. 
Following method is used to publish the report to the server.
'Function to publish the report to report server
Private Sub PublishReports(ByVal FilePath As String, ByVal ReportName As String, ByVal TargetFolder As String)
Dim ReportDefinition As [Byte]() = Nothing
Dim warnings as Warning() = Nothing
Dim description As New [Property]
Dim properties(0) As [Property]
' Open rdl file
Dim rdlfile As FileStream = File.OpenRead(FilePath)
ReportDefinition = New [Byte](rdlfile.Length - 1) {}
rdlfile.Read(ReportDefinition, 0, CInt(rdlfile.Length))
  'Set Report Description
  description.Name = "Description"
  description.Value = ""
  properties(0) = description
  'Create a Report
warnings = rs.CreateReport(ReportName, TargetFolder, True,   ReportDefinition,Properties)
Console.WriteLine("Information: " + ReportName + " published successfully")
Catch e as Exception
  Console.Writeline (e.Message)
End Try
End Sub
The method PublishReports takes three arguments like Filepath, Reportname and TargetFolder. The FilePath is the path where the report is present in the local system, Report name is the name of the report and the TargetFolder is the folder in the server where we are publishing the reports. The method will open the report file, read the stream data and then create the report in the server.
The following method set the datasource for the reports
'Function to assign the datasource for the report
Private Sub SetDataSource(ByVal FolderName As String, ByVal ReportName As String, ByVal DataSourceFolder As String, ByVal DataSourceName As String)
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim ReportPath As String = FolderName + "/" + ReportName
Dim DataSources(0) As DataSource
Dim DsRef As New DataSourceReference
DsRef.Reference = DataSourceFolder + "/"+ DataSourceName
Dim objDS As new DataSource
objDS.Item = CType (DsRef, DataSourceDefinitionOrReference)
objDS.Name = "DataSource1"
DataSources(0) = objDS
rs.SetItemDataSources(ReportPath, DataSources)
Console.Writeline ("Information: DataSource " + objDS.Name + " is set to the report " + ReportName)
Catch e As Exception
Console.Writeline (e.Message)
End Try
End Sub
Note: In objDS.Name = “DataSource1″, you have yo replace the DataSource1 with the name of the shared datasource in the reports that you are planning to deploy. For demo purpose we assume that all the reports use the same shared datasource.
The SetDataSource method takes four arguments like Foldername, ReportName, DataSourceFolder, DataSourcename and set the datasource to the report. FolderName is the path of the folder which contains the reports. ReportName is the name of the report to which we need to set the datasource. DataSourceFolder is the path of the Folder which contains the datasources. DataSourceName is the  name of the datasource which we need to set to the report.
Delete the  reports and DataSources using the below method.
'Function to Delete the report from report server
Private Sub DeleteItem(ByVal FolderName As String, ByVal ItemName AS String)
rs.DeleteItem (FolderName + "/" + ItemName)
Console.WriteLine("Information: " + ItemName + " deleted Successfully")
Catch e As Exception
End Try
End Sub
The method DeleteItem take two arguments Foldername and ItemName and delete the item. The FolderName has the path of the folder and the ItemName has the name of the item to be deleted.
Following is the main method which is used to call the all other methods mentioned above.
'Main method is used to call the methods to create folder, Reports, DataSource, and assign the datasource to the reports
Public Sub main()
Dim ReturnValue As Integer = 0
'Create the Folders to deploy the datasources
ReturnValue = CheckItemAvailability(TargetFolder, DataSourceFolderName, "Folder")
If ReturnValue = 0
CreateFolder (DataSourceFolderName, TargetFolder, "", "False")
Console.Writeline("Information: Folder "+ DataSourceFolderName + " Created Successfully.." )
Console.Writeline("Information: Folder "+ DataSourceFolderName + " already Exists.." )
End If
' create a folder to deploy the reports
ReturnValue = 0
ReturnValue = CheckItemAvailability(TargetFolder, ReportFolderName, "Folder")
If ReturnValue = 0
CreateFolder (ReportFolderName, TargetFolder, "", "False")
Console.Writeline("Information: Folder "+ ReportFolderName + " Created Successfully.." )
Console.Writeline("Information: Folder "+ ReportFolderName + " already Exists.." )
End If
' Create the datasource if it does not exist
ReturnValue = 0
ReturnValue = CheckItemAvailability(DataSourceFolder, DataSourceName, "DataSource")
If ReturnValue = 0
CreateDataSource (DataSourceFolder, DataSourceName, ServerName, DataBaseName)
Console.Writeline("Information: DataSource " + DataSourceName + " Created Successfully..")
Console.Writeline("Information: DataSource " + DataSourceName + " already Exists..")
End If
'Deploy the reports if it does not exist and Set the datasource
Dim DirectoryList As String() = Directory.GetDirectories(ReportFolder)
Dim DirectoryName As String
For Each DirectoryName in DirectoryList
Dim IndexPosition As Integer = 0
'Get the index position of the folder name in the directory
IndexPosition = DirectoryName.LastIndexOf("\")
'Get the Folder name and Check it with the folder name in your local system
IF DirectoryName.Substring(IndexPosition + 1) = ReportFolderName
'Get the full path of the reports
Dim fullPath As String
Dim DirectoryFolderName As String = DirectoryName.Substring(IndexPosition + 1)
fullPath = ReportFolder + "\" + DirectoryFolderName
'Get all the files in the report folder
Dim FileList As String() = Directory.GetFiles(fullPath)
Dim FileName As String
Dim ReportName As String
For Each FileName in FileList
'Get the Report Name excluding the extension
IF  FileName.Substring(FileName.Length - 4) = ".rdl"
ReportName = System.IO.Path.GetFileName(FileName).Replace(".rdl","")
End If
ReturnValue = 0
'Get the reportFolder path to create the report
Dim TargetReportPath As String
TargetReportPath = TargetFolder + ReportFoldername
ReturnValue = CheckItemAvailability(TargetReportPath, ReportName, "Report")
If ReturnValue = 1
'Delete the existing item in the same path
DeleteItem (TargetReportPath, ReportName)
Console.WriteLine("Information: " + ReportName + " does not exist to delete")
        End If
'Publish the report
PublishReports(FileName, ReportName, TargetReportPath)
'Check the availability of the datasource
ReturnValue = 0
ReturnValue = CheckItemAvailability(DataSourceFolder, DataSourceName, "DataSource")
'Set Datasource to the deployed report if the datasource exists
If ReturnValue = 1
SetDataSource(TargetReportPath, ReportName , DataSourceFolder, DataSourceName)
Console.Writeline("Information: " + DataSourceName +  " DataSource does not exist")
End If
End IF
Catch e As Exception
Console.Writeline (e.message)
End Try
End Sub
The main method is used to call the other methods to create the folder in report server, Deploy the reports, create datasource and assign the datasource to the reports.
Finally let us see how we will execute the script file using the batch file. Copy the below mentioned information in a batch file and run the batch file.
set TargetFolder=/
set ReportFolder=D:\Project\Deploy_Report
Set DataSourceFolder=/Data Sources
Set DataSourceFolderName=Data Sources
Set ReportFolderName=Order Details
Set DataSourceName=dsName
Set ServerName=localhost
Set DataBaseName=Test
rs -i Deploy_Report.rss -s %TargetURL% -v TargetFolder="%TargetFolder%" -v ReportFolder="%ReportFolder
  1. The batch file(.cmd) and the Report Server Script(.rss) should be placed in the same folder. If they are not placed in the same folder then update the rs command in the batch file accordingly.
  2. As per the same code mentioned above the complete path in which the reports are placed is (D:\Project\Deploy_Report\Order Details) 
Following are the details that we need to pass to the rss script file.
Target Folder – The “/” in the target folder parameter is passed to create the folder under the Home folder in the report manager.
TargetURL - Pass the report server URL where we need to deploy the reports and datasources. (eg. http://localhost/reportserver).
ReportFolder – Is the path of the folder in the local computer where you have kept the reports. (eg. D:\Project\Deploy_Report).
DataSourcesFolder - Is the folder in the server where the datasources are available. (eg. /Data Sources).
DataSourcesFolderName – Is the name of the data sources folder (eg. Data Sources).
ReportFolderName – Is the name of the folder in local computer where the reports are placed and the report folder will be created with same name in report server to deploy the reports. (eg. Order Details)
DataSourcename - Is the name of the datasource(eg. dsName).
ServerName - The name of the server used in the datasource to connect to the database (eg. localhost).
DatabaseName - The name of the database used in the datasource (eg. Test).
By entering the details in the above mentioned variables and executing the batch file we can deploy the reports on to the report server.
The Source code files are renamed as .docx files and attached with this article..
Batch File – Batch File
Report Server Script File – Deploy_Report
Copy the contents of the Batch File and save it as .bat file. Copy the contents of the Deploy_Report and save it as .rss file and then update the batch file information. The files are ready for the report deployment. Update the batch file accordingly and then start using it.

No comments:

Post a Comment