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.
-
Go to Report Manager
-
Upload the rdl file using the Upload Files link on the toolbar and navigate to the rdl file in your system.
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.
A fourth alternative does exist…
http://jcrawfor74.wordpress.com/2010/07/09/how-to-copy-reporting-services-reports-off-a-reporting-server-and-production-deployment/
LikeLike
Wonderful work!! Really helped me out in migrating from an SSRS 2000 installation 😦 to SSRS 2008!
LikeLike
Thanks for your post. It is good to know about these options.
LikeLike
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.
LikeLike
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.
LikeLike
is this work on x64 ?
LikeLike
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 !!!!
LikeLike
Hi Stefan,
This code is from a live project, and should work. If you can post your code or send to me directly, trhymond@gmail.com, I can take a look.
LikeLike
I every time spent my half an hour to read this weblog’s articles or reviews every day along with a cup of coffee.
LikeLike
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.
LikeLike
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!
LikeLike
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.
LikeLike
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
LikeLike
thanks for the information, am facing one error Could not connect to server: http://localhost/reportserver_vessel/ReportService2005.asmx, please help me
LikeLike