SQL Server Reporting Services – Deploying rdl Files


When it comes to deploying rdl files, it can be  very tedious, especially if  you have hundreds of rdl files and server test regions to deploy. There is no good tools available to automate the rdl deployment.

Here I will explain 3 available options to deploy rdl files.The samples and screen shots are based on SQL Server 2008 R2 and vary slightly in older versions of SQL Server. You can download all code sample here (SQL Report UploaderrsUtility Sample).

  • Report Manager
  • rs Utility
  • Reporting Web Service

Report Manager

First I will explain how to deploy your rdl files using Report Manager, more often using http://localhost/Reports.

  1. Go to Report Manager
  2. Create a new folder to keep all your reports or use an existing folder. Click on the New Folder button to create a new folder.
    image
  3. Create a Data Source, if you don’t have one already. Click on New Data Source  link on the toolbar  to create a new data source.
    image
  4. Upload the rdl file using the Upload Files link on the toolbar and navigate to the rdl file in your system.
  5. After upload, click on the Detail View link on the toolbar and edit the report  selecting  Manage  link.
    image
  6. Under the Data Source link, assign the previously created Data Source to the report.   Click on the Apply button to save your selection.
    image

This is manual process and can be used if you have small number of reports and you don’t have to deploy them frequently.

rs Utility

Developers and report server administrators can perform operations on a report server through the use of the rs utility (RS.exe). Using this utility, you can programmatically administer a report server using scripts written with Microsoft Visual Basic. Reporting Services scripts can be used to run any of the Report Server Web service operations. Scripting can be used to copy security to multiple reports on a server, to add and delete items, to copy report server items from one server to another and more.

RS.exe is located at \Program Files\Microsoft SQL Server\100\Tools\Binn. You can run the utility from any folder on your file system. To run the tool, you must have permission to connect to the report server instance you are running the script against. You can run scripts to make changes to the local computer or a remote computer. To make changes to a report server installed on a remote computer, specify the remote computer in the -s argument.

Syntax
rs {-?}
{-i input_file=}
{-s serverURL}
{-u username}
{-p password}
{-e endpoint}
{-l time_out}
{-b batchmode}
{-v globalvars=}
{-t trace}
Example
rs –i c:\scriptfiles\script_copycontent.rss -s http://localhost/reportserver

The .rss file contain the code to read the rdl file from the local system, upload the file to report server and set data source. A sample .rss file is shown below.

 1: Public Sub Main()
 2:     Dim folder As String = PARENTFOLDER
 3:     Dim name As String = RDLNAME
 4:     Dim data As String = DATASOURCE
 5:
 6:     Dim parent As String = "/" + folder
 7:     Dim location As String = name + ".rdl"
 8:
 9:     Dim overwrite As Boolean = True
 10:     Dim reportContents As Byte() = Nothing
 11:     Dim warnings As Warning() = Nothing
 12:
 13:     Dim fullpath As String = parent + "/" + name
 14:
 15:     'Common CatalogItem properties
 16:     Dim descprop As New [Property]
 17:     descprop.Name = "Description"
 18:     descprop.Value = ""
 19:     Dim hiddenprop As New [Property]
 20:     hiddenprop.Name = "Hidden"
 21:     hiddenprop.Value = "False"
 22:
 23:     Dim props(1) As [Property]
 24:     props(0) = descprop
 25:     props(1) = hiddenprop
 26:
 27:     'Read RDL definition from disk
 28:     Try
 29:         Dim stream As FileStream = File.OpenRead(location)
 30:         reportContents = New [Byte](stream.Length-1) {}
 31:         stream.Read(reportContents, 0, CInt(stream.Length))
 32:         stream.Close()
 33:
 34:         warnings = RS.CreateReport(name, parent, overwrite, reportContents, props)
 35:
 36:         If Not (warnings Is Nothing) Then
 37:             Dim warning As Warning
 38:             For Each warning In warnings
 39:                 Console.WriteLine(Warning.Message)
 40:             Next warning
 41:         Else
 42:             Console.WriteLine("Report: {0} published successfully with no warnings", name)
 43:         End If
 44:
 45:         'Set report DataSource references
 46:         Dim dataSources(0) As DataSource
 47:         Dim dsr0 As New DataSourceReference
 48:         dsr0.Reference = region + "/MyDataSource"
 49:         Dim ds0 As New DataSource
 50:         ds0.Item = CType(dsr0, DataSourceDefinitionOrReference)
 51:         ds0.Name = data
 52:         dataSources(0) = ds0
 53:
 54:         RS.SetItemDataSources(fullpath, dataSources)
 55:
 56:         Console.Writeline("Report DataSources set successfully")
 57:         Console.WriteLine("Report: {0} published successfully", name)
 58:
 59:     Catch e As IOException
 60:         Console.WriteLine(e.Message)
 61:     Catch e As SoapException
 62:         Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText + " (" + e.Detail.Item("Message").InnerText + ")")
 63:         Console.WriteLine("Report: {0} published with error", name)
 64:     End Try
 65: End Sub
 66:

If you several rdl files to deploy, you can write a batch script to automate the deployment. Create batch file name it as deploy_rdl.cmd.

 1: @echo off
 2: ::Script Variables
 3: SET LOGFILE="RS Scripter log.txt"
 4: SET SCRIPTLOCATION=C:\SSRS Scripts\TestReports
 5: SET REPORTSERVER=http://localhost/Reports
 6: SET RS="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\RS.EXE"
 7: SET TIMEOUT=60
 8:
 9: ::Clear Log file
 10: IF EXIST %logfile% DEL %logfile%
 11:
 12: ::Run Scripts
 13: %rs% -i "%SCRIPTLOCATION%\TrendReports.rss" -s %REPORTSERVER% -l %TIMEOUT% -v PARENTFOLDER="MyTestReports" -v RDLNAME="TrendReport" -v DATASOURCE="MyDataSource" >>%LOGFILE% 2>&1
 14: ECHO. >>%LOGFILE%
 15:
 16: %rs% -i "%SCRIPTLOCATION%\SLAReports.rss" -s %REPORTSERVER% -l %TIMEOUT% -v PARENTFOLDER="MyTestReports" -v RDLNAME="SLAReport" -v DATASOURCE="MyDataSource" >>%LOGFILE% 2>&1
 17: ECHO. >>%LOGFILE%
 18:
 19: ECHO. >>%LOGFILE%
 20: ECHO Finished Load at %DATE% %TIME% >>%LOGFILE%
 21: ECHO. >>%LOGFILE%

You download the code Sample for the above scripts  here.

Reporting Web Service

SQL Server Reporting Services provides access to the full functionality of the report server through the Report Server Web service. The Report Server Web service is an XML Web service with a SOAP API. It uses SOAP over HTTP and acts as a communications interface between client programs and the report server. The Web service provides two endpoints – one for report execution and one for report management – with methods that expose the functionality of the report server and enable you to create custom tools for any part of the report life cycle.

Programming Diagram – Web Service Development Options

IC156257

I will explain how to create a WCF client to access SQL Server Reporting Services , upload a report to the report server, Create Data Source and assign data source to a report.

Create a project using your favorite visual Studio IDE . Here I use Visual Studio 2008 and creating  a windows application project.

Add a Service reference to the Web Service URL The URL for the report execution endpoint of the Report Server Web service might look like this:

http://localhost:8080/ReportServer/ReportService2005.asmx

The URL contains the domain in which the Report Server Web service is deployed, the name of the folder containing the service, and the name of the discovery file for the service.

Note: the name of the discovery file is ReportService2005.asmx for SQL Server 2008.

Select the project and add a class file ReportUploader.cs .

Add a using (Import in Visual Basic) directive to the Report Server Web service reference.

 1: using Rhymond.ReportServices.Uploader.ReportingServices;

Declare the client variable

 1: /// <summary>
 2:        /// The sql reporting service client
 3:        /// </summary>
 4:        private ReportingService2005SoapClient client;

Create the client instance and setup permissions to access the report services.

 1: /// <summary>
 2: /// Initializes a new instance of the <see cref="ReportUploader"/> class.
 3: /// </summary>
 4: /// <param name="reportDefition">The report defition.</param>
 5: /// <param name="region">The region.</param>
 6: public ReportUploader(string reportDefition, string region)
 7: {
 8:     this.client = new ReportingService2005SoapClient("ReportExecutionServiceSoap");
 9:     this.client.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
 10:     this.client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
 11: }

Create a folder on the reporting server.

 1: // Create Folder if the folder doesn't exists
 2: CatalogItem[] result = null;
 3: this.client.ListChildren(remoteRoot + "/" + this.Region, false, out result);
 4: if (result != null
 5:     && result.Where(i => i.Type == ItemTypeEnum.Folder && i.Name == report.RemotePath).Count() == 0)
 6: {
 7:     BatchHeader batch1 = this.GetBatchHeader();
 8:     this.client.CreateFolder(batch1, report.RemotePath, remoteRoot + "/" + this.Region, this.GetReportProperties(""));
 9:     this.client.ExecuteBatch(batch1);
 10: }

In SQL server 2008 the server calls are executed in batch. You have to create a BatchHeader and call client.ExecuteBatch with the BatchHeader.

 1: /// <summary>
 2: /// Gets the batch header.
 3: /// </summary>
 4: /// <returns></returns>
 5: private BatchHeader GetBatchHeader()
 6: {
 7:     string batchId = string.Empty;
 8:     this.client.CreateBatch(out batchId);
 9:
 10:     BatchHeader header = new BatchHeader();
 11:     header.BatchID = batchId;
 12:
 13:     return header;
 14: }

Upload a Report.

 1: // Upload Report
 2: Warning[] warnings;
 3: // Read rdl file contents to a byte array
 4: byte[] definition = this.GetReportContents(Path.Combine(Path.Combine(localRoot, report.LocalPath), report.Name) + ".rdl");
 5: BatchHeader batch2 = this.GetBatchHeader();
 6: this.client.CreateReport(batch2, report.Name, remoteReportPath, true, definition, this.GetReportProperties(report.Description), out warnings);
 7: this.client.ExecuteBatch(batch2);
 8:
 9: if (warnings != null && warnings.Length > 0)
 10: {
 11:     foreach (Warning warn in warnings)
 12:     {
 13:         Console.WriteLine(String.Format("{0}: {1}", warn.ObjectName, warn.Message));
 14:     }
 15: }

Create Data Source

 1: /// <summary>
 2: /// Creates the data source.
 3: /// </summary>
 4: /// <param name="rdlDataSource">The RDL data source.</param>
 5: public void CreateDataSource(ReportDataSource reportDataSource)
 6: {
 7:     DataSourceDefinition dsd = new DataSourceDefinition();
 8:     dsd.ConnectString = reportDataSource.ConnectionString;
 9:     dsd.Enabled = true;
 10:     dsd.Extension = reportDataSource.DataSourceType.ToString();
 11:
 12:     if (reportDataSource.WindowsAuth)
 13:     {
 14:         dsd.WindowsCredentials = true;
 15:     }
 16:     else
 17:     {
 18:         dsd.CredentialRetrieval = CredentialRetrievalEnum.Store;
 19:         dsd.UserName = reportDataSource.UserName;
 20:         dsd.Password = reportDataSource.Password;
 21:     }
 22:
 23:     BatchHeader batch = this.GetBatchHeader();
 24:     client.CreateDataSource(batch, reportDataSource.Name, reportDataSource.DataSourcePath, true, dsd, this.GetReportProperties(reportDataSource.Description));
 25:     this.client.ExecuteBatch(batch);
 26: }

Assign  data source to a report. Some reports may have multiple data sources, in that case GetItemDataSource return an array of datasources. Find your data source in that array and update he DataSourceReference.

 1: DataSourceReference dsRef = new DataSourceReference();
 2: dsRef.Reference = rdlDataSource.DataSourcePath + "/" + rdlDataSource.Name;
 3:
 4: DataSource[] dataSources = null;
 5: this.client.GetItemDataSources(remoteReportPath + "/" + rdlName, out dataSources);
 6: if (dataSources != null)
 7: {
 8:     if (dataSources.Length > 1)
 9:     {
 10:         DataSource ds = dataSources.Where(d => d.Name == rdlDataSource.Name).FirstOrDefault();
 11:         if (ds != null)
 12:         {
 13:             ds.Item = dsRef;
 14:
 15:             dataSources = new DataSource[1];
 16:             dataSources[0] = ds;
 17:
 18:         }
 19:     }
 20:     else
 21:     {
 22:         dataSources[0].Item = dsRef;
 23:     }
 24: }
 25: else
 26: {
 27:     dataSources = new DataSource[1];
 28:     dataSources[0].Item = dsRef;
 29: }
 30:
 31: BatchHeader batch = this.GetBatchHeader();
 32: this.client.SetItemDataSources(batch, remoteReportPath + "/" + rdlName, dataSources);
 33: this.client.ExecuteBatch(batch);

Note: If you try to create a new DataSource Array and assign DataSource reference, you might get a “Invalid input XML” error.  So you must use GetItemDataSoruces method to get all available data source for the report and update the DataSourceReference.

You can down load the  complete project here. This is a sample but working code; Feel free to make any changes; suggestions are always welcome.

About these ads

8 responses to “SQL Server Reporting Services – Deploying rdl Files

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: