Deploying Reports Using RS Utility

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.

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

'Function to check the report, data source and folder availability
Private Function CheckItemAvailability(ByVal ItemPath As String, ByVal ItemName As String, ByVal ItemType As String)
Dim folderItems As CatalogItem()
Dim folderItem As CatalogItem
Dim ReturnValue As Integer = 0

folderItems = rs.ListChildren(ItemPath, false)
For Each folderItem In folderItems

If ItemType = "Folder"
If folderItem.Name = ItemName And folderItem.Type = 1
ReturnValue = 1
  End If
Else If ItemType = "Report"
If folderItem.Name = ItemName And folderItem.Type = 2
ReturnValue = 1
  End If
Else If ItemType = "DataSource"
If folderItem.Name = ItemName And folderItem.Type = 5
ReturnValue = 1
  End If
End If
Next
Return ReturnValue
End Function

 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 TargetURL=http://localhost/reportserver
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%" -v DataSourceFolder="%DataSourceFolder%"  -v DataSourceFolderName="%DataSourceFolderName%" -v ReportFolderName="%ReportFolderName%" -v DataSourceName="%DataSourceName%" -v ServerName="%ServerName%" -v DataBaseName="%DataBaseName%"

Note: 

  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.

Advertisements

16 thoughts on “Deploying Reports Using RS Utility

  1. Pingback: Back up reports Using rs utility « bilearnings

  2. it help me a lot ..
    thank you very much for this valuable script specially it has delete report functionality which i am looking for. .

      • Hi Yogi

        I have Folder data sources created successfully message
        I have folder deploy_report created successfully message
        I have datasource datasource3 created succesfully message

        but I have no messages regarding the report.
        no succeded messages or failed messages.

        what could be happend?

        1 have one report (.rdl file) in the Deploy_Report folder

      • Hi Luis, If you have the report name “Report1” it should display the following message “Information: report1 Published Successfully”. If you do not get this message then the report is not published.
        To cross check open the report manager URL. Then click on the folder which is created “Deploy_Report”. It should contain your report. If it is not there then there is some issue.. Which you need to figure it out.. Since it is script you need to write output messages in appropriate location and then verify it..

  3. Can this code be used to deploy reports into folders that are already located on the SSRS home screen? Or is it only able to deploy to the home screen. I seem to get an error every time I try to name the target folder anything other that “/”

    • Hi Dan,

      Whatever name that you are specifying in the target folder should already be present in the report server. If it has the folder structure this tool will create a new folder under the folder specified in the target folder variable. It can deploy the reports under any folder in ssrs.

  4. The last deployed I mean the report which was deployed at the last, After it got successfully deployed to the server it has been automatically getting deleted. I could see the same info in cmd prompt.
    Could you please help me out with this

    • Hi,
      If you read the post it actually deletes the reports if it is existing and then it will deploy it. If you are not able to see the reports then it might not have deployed successfully.

  5. hi , i am unable to deploy succesfully by using this scripts, i am getting the error say the “Unrecognised argument report folder =C:\Program%20%Files\Microsoft%20%SQL%20%Server\110\Tools\Binn\Reports”. this the path where i kept my .rdl files. this is automation is very important for me as we have delivery in next 2days

    Waiting for valuable reply. Thanks in advance.

    Krishna

  6. Hi there,

    I am using above script and am unable to deploy reports with shared data sources.

    Also, I have two different data sources used in my reports project. Can we use the same script ?

    • Hi Manisha,

      Yes you can deploy the reports which has shared data sources. For using two different data source yes you can use the same script. However you need to modify it a bit. Please refer to the existing data source assignment and similarly you need to implement for the second one.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s