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 Uploader,  rsUtility 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.
   
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.
   
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.
   
6. Under the Data Source link, assign the previously created Data Source to the report.   Click on the Apply button to save your selection.
   

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

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.