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

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

  • John

    Wonderful work!! Really helped me out in migrating from an SSRS 2000 installation :-( to SSRS 2008!

  • Sergey

    Thanks for your post. It is good to know about these options.

  • runescape classic

    Hi there all, here every one is sharing these familiarity,
    so it’s fastidious to read this web site, and I used to go to see this website daily.

  • James

    This is really helpful. But when I try to run it, is says that the remote path is incorrect. In the app.config I am putting remoteRoot=”/” and remotePath=”Test”

    I have the server at “http://localhost/ReportServer/” and on this server I have the folder called “Test” where I want to deploy all the reports. I am using this code so that we can put all the reports and datasource inside one folder and using that folder customers can upload all the reports and datasource to their local report server (with this help of your code). I have updated the webservice reference to point it to correct one.

    I am using Reporting Web Service for it.

    Please help.

  • levi

    is this work on x64 ?

  • Stefan

    Is there a step by step guide available how to use this scripts? I need the version with rs.exe. I can’t get it to run. Errors over errors. Help !!!!

  • Bauchmuskeltraining

    I every time spent my half an hour to read this weblog’s articles or reviews every day along with a cup of coffee.

  • short-trm alcohol effects

    Aw, this was an incredibly good post. Spending some time and actual effort to produce a great article… but what can I say… I hesitate a whole lot and never seem to get nearly anything done.

  • Grow XL And BLast XL

    Hi! I’ve been reading your weblog for a long time now and finally got the bravery to go ahead and give you a shout out from New Caney Texas! Just wanted to mention keep up the good job!

  • virtual teachers

    A massive open online course. Freasyway is an online course aimed at large-scale interactive participation and open access via the
    web. http://www.freasyway.com or http://edu.freasyway.

    com

    In addition to traditional course materials such as videos, readings, and problem sets, Freasyway provide interactive user forums, Sosharing
    wall, class way that help build a community
    for the students, professors etc. Freasyway are a recent development in distance education.
    Features associated with early Freasyway, such as open licensing of content,
    open structure and learning goals, and connectivism may not be present in all Freasyway projects.

    List of free online courses. Freasyway offered the best initiatives.
    ‎ Freasyway Top Jobs office, The Student Employment works with faculty and international partners, the community, and all University departments in
    many countries to create term-time and summer employment.

    creates and provides quality, innovative online learning opportunities to anyone
    who wants to improve the technology, literacy
    and math skills needed to be successful in both work and
    life. By delivering over 5790.000 different lessons to millions of people in over 360 countries and
    territories ABSOLUTELY FREE, Freasyway is a worldwide leader in online education.

    View one tutorial. Complete a whole class. We believe there’s freedom in the ability to learn what you want, when you want, regardless of income or circumstances. Here you can watch the video http://www.youtube.com/watch?v=aCl8yHuAbHo

    View one tutorial. Complete a whole class. We believe there’s freedom in
    the ability to learn what you want, when you want, regardless of income or circumstances.

    Freasyway educational content online, Creates New Approach
    within Math Program
    Freasyway multimedia learning, technology-enhanced learning

    Freasyway Manager allows you to easily manage and supervise the
    online learning activity of your learners/students. Teachers,
    trainers, tutors, and HR managers
    Learning resources for adults, children, parents and teachers:
    find online courses, video clips, educational games and revision
    activities.

    Learning resources for everyone. Learning resources for home and school, Online learning,
    support and advice. Free online courses from the world’s leading universities. …
    To start learning 40 foreign languages, please see our extensive collection called Learn Languages brings together high-quality cultural & educational media for the worldwide lifelong learning community. Freasyway Friends Network is an social connection developed for provides a simple way to represent human relationships using links. Freasyway Friends Network enables web authors to indicate relationships to the people in their blogrolls by adding one or more keywords as the rel attribute to their links.
    Freasyway Sosharing a powerful social network modelling tool. It also has the great advantage of simplicity: there is no need of any special skills to use it, except curiosity!

    This tool is used in a large number of contexts, which is why it took us to heart to propose a guide, fit to a wide audience.
    We offer a tutorial based on your own data from Freasyway, which obviously can be used with various kinds of data!
    Freasyway, Friends network mapping. The Difference Between Networking and Making Friends | The Daily. The Difference Between Networking and Making Friends
    Freasyway contact sharing, networking and making friends look pretty similar. You’re meeting new people, finding common interests, and hoping that the relationship will continue.
    Freasyway Networking is about building rapport, having substantive conversations, and finding commonalities with other professionals in a limited amount of time. Yes, it’s an opportunity to connect with others—but it’s more about advancing your professional goals than it is about getting people to like you or boosting your social scene.

  • kunal

    Nice post.
    if you want to deploy to SSRS 2012 then refer to the following link –

    http://sanganakauthority.blogspot.in/2014/02/automated-deployment-of-rdl-files-to.html

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.

Join 37 other followers

%d bloggers like this: