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 http://msdn.microsoft.com/en-us/library/ms162839.aspx
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.
Following method is used to create the datasource
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.
The following method set the datasource for the reports
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.
Following is the main method which is used to call the all other methods mentioned above.
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.
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 http://msdn.microsoft.com/en-us/library/ms162839.aspx
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
Try
'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
Console.Writeline(e.Message)
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
Try
rs.CreateDataSource(DataSourceName,
DataSourcePath, false, dsDefinition, Nothing)
Catch
e As Exception
Console.Writeline(e.Message)
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]
Try
' Open rdl file
Dim
rdlfile As FileStream =
File.OpenRead(FilePath)
ReportDefinition = New [Byte](rdlfile.Length
- 1) {}
rdlfile.Read(ReportDefinition, 0,
CInt(rdlfile.Length))
rdlfile.Close()
'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)
Try
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)
Try
rs.DeleteItem (FolderName +
"/" + ItemName)
Console.WriteLine("Information:
" + ItemName + " deleted Successfully")
Catch
e As Exception
Console.Writeline(e.Message)
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()
Try
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.." )
Else
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.." )
Else
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..")
Else
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)
Else
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)
Else
Console.Writeline("Information:
" + DataSourceName + " DataSource does not
exist")
End
If
Next
End
IF
Next
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
Note:
- 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.
- 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