SQL Reporting Services, Technology

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.

Advertisements
Cloud Computing, Technology

Comparing Cloud Services

A cloud service allows the us to store  media and documents on the internet, so you can access it from anywhere, anytime from any device with an internet connection.  The general idea of the cloud services is to store and access data from the internet and opposed to keeping  it on the local hard drive.  Ideally with cloud services you can access any type of media and document such as video,  photos , e-book and your work and personal documents. Now with cloud services you can juggle around your data on multiple devices.

With the recent announcement of iCloud, Apple joins Google, Microsoft and Amazon in their push for cloud services. But cloud services vary between companies so much that the buzzwords can be very confusing. What are you getting from this service?  What is an online storage?  Does it stream video? How secure my data ?

The below chart give you a side-by-side comparison of  6 major cloud services.

  • .
  • Features
  • Device
  • Music
  • Apple
    iCloud
  • Includes 5 GB of free online storage, synchronization for music, photos, apps, documents, iBooks, contacts, e-mail and calendars;
  • Macs and iOS 5 (Windows PCs get PhotoStream and some basic features only)
  • All iTunes-purchased music can be shared between devices; iTunes Match: $25 per year to push 25,000 tracks in your library to be shared through iCloud
  • Google Apps
  • Includes 1 GB free online storage for Google Docs, 1 GB free storage for Picasa, 7 GB free storage for Gmail; streaming music, synchronized documents, contacts, e-mail, calendars; expandable to 16 TB for $4,000 per year
  • All devices with a web browser.
  • Google Music Beta lets you upload up to 20,000 tracks from your own library
  • Amazon
    Cloud Drive
  • Includes 5GB free online storage; additional storage can be purchased for $1 per gigabyte per year.
  • All devices compatible with Adobe Flash.
  • Includes Cloud Player music-streaming application.
  • Microsoft
    Windows Live SkyDrive
  • Includes 25 GB free storage for files and synchronization for photos.
  • Windows PC, Mac, Windows Phone 7
  • None
  • Dropbox
  • Includes 2GB free storage, upgradeable to 100 GB for $200 per year
  • All devices with a web browser or Dropbox client.
  • Built-in audio player in web interface and iOS client.
  • Box
  • Includes 5GB free storage, upgradeable to 50 GB for $20 per month
  • All devices with a web browser or Boxnet client.
  • None

Here are some more detail on how these services work.

iCloud Service is designed to work  automatic and effortless as if it were invisible. It is seamlessly integrated into your apps, so you can access your contents on all  your devices. iCloud stores music, photos, videos, calendar, apps, documents and more. And wirelessly pushes them to all your devices, automatically.

iCloud is so much more than a hard drive in the sky. It’s the effortless way to access just about everything on all your devices. iCloud stores your content so it’s always accessible from your iPad, iPhone, iPod touch, Mac, or PC (**with limited features). No syncing required. No management required. In fact, no anything required. iCloud does it all for you.

When you sign up for iCloud, you automatically get 5GB of free storage. And that’s plenty of room, because of the way iCloud stores your content. Your purchased music, apps, books, and TV shows, as well as your Photo Stream, don’t count against your free storage. Since your mail, documents, Camera Roll, account information, settings, and other app data don’t use as much space, you’ll find that 5GB goes a long way. And if you need more storage, you can easily purchase a storage upgrade right from your device.

Apple has left some questions unanswered as to whether iCloud will have a web app interface for accessing these services from any device with a browser, like MobileMe did. However, we believe it’s shortsighted to think that iCloud would not eventually have a web app suite to complement the aforementioned services.

Google Apps offer Reliable, secure online applications. It is a collection of web-based programs and file storage that run on a web browser.  There is no one stop destination that host your media and documents.  You have to use Picasa for your photos, Google Music for your music, GMail for your emails and Google docs for your documents.

Gmail offers more than 7 Gb for messages and attachements and other service offers at least 1 GB of free space, and you can plunk down an extra $5 per year to add 20 gigs for most of its services. You can rent up to 16 TB each year for $4,000.

Amazon Cloud Drive is like a personal hard drive in the cloud. Store your music, videos, photos, and documents on Amazon’s secure servers. All you need is a web browser to upload, download, and access your files from any computer. Cloud Drive comes with 5 GB of free storage—enough space to store up to 1000 songs. When you purchase songs or albums from the Amazon MP3 Store, you can now save your purchases to your Cloud Drive. All your purchases are backed up and available for you to download at any time. Even better, you can listen to your music from any web-connected computer with Amazon Cloud Player. You can only access the files from a device that supports Flash. (That means Cloud Drive is useless for any iPhone or iPad customer, since the devices do not support Flash.) . Amazon offer paid storage plans with up to 1000 GB of space and that cost about $1 per 1 Gig of storage.

Like Amazon Cloud Drive, SkyDrive is also your storage place in the cloud. Upload your files and photos so you can access them from anywhere, and share them to collaborate with others. You get 25 GB free, and that’s all you can have — no options to expand. You can throw whatever file you want into the SkyDrive. SkyDrive has some integration with Windows Phone 7. Snap a photo or shoot a video on your Windows phone, and it goes straight to the SkyDrive. With SkyDrive, you can also create, view, edit, and share Microsoft Office docs saved in Word, Excel, PowerPoint, and OneNote for free online.

SkyDrive has permission controls that let you share your files and Office docs with specific people. SkyDrive even has permission settings that allow people to view your files but not edit or delete them. If you’re collaborating on a document online, and you don’t like where an idea has headed, you can simply roll back to an earlier version of the document instead of starting over.

Dropbox, the hottest cloud solution in the startup world. It’s basically a web-connected folder that lives on your PC or smartphone. Any file you save to Dropbox also instantly saves to all your devices, and the Dropbox website. The Dropbox client works the same on every major platform — Windows, Mac, Linux, iOS and Android — and you can also access your files through the Dropbox web interface on any device with a web browser.  When you sign u p, you get 2GB of Dropbox for free, with subscriptions up to 100GB available. It works even when offline. You always have your files, whether or not you have a connection.

Box lets you store documents, media and all of your content online, so you can access, manage and share them from anywhere. You can access Box with Google Apps and several other mobile apps such as GoodReader, Notarize and QuickOffice. You can also access Box on mobile devices.  The Box client works the same on every major platform — Windows, Mac, Linux, iOS and Android — and you can also access your files through the Box web interface on any device with a web browser. When you signup for Personal account, you get 5 GB of Box for free and have a option of buying up to 50 GB for about $20.

Conclusion

  • Microsoft Windows Live give you the largest storage: 25GB, but is not easy to use.
  • Google provides many distributed services with at least 1GB free for each.
  • Dropbox is a good service for storing and sharing data but it’s the most expensive.
  • Amazon Cloud Drive: you have 5GB free storage. If you use iOS devices which don’t support Flash, it’s not easy to access your data.
  • iCloud looks very good but to use it efficiently, you need iOS devices or Macs.
  • Box is a good service for storing and sharing data but it’s the most expensive
The cloud service you choose will likely rely on the device you own. For people with an iPhone and a Mac, iCloud is an elegant solution for tying the two experiences together. Android and Windows customers will probably find good value in Amazon’s Cloud Drive, Google’s cloud suite or Microsoft’s Windows Live. Finally, Dropbox costs a bit more than the other services, but its cross-platform compatibility makes it ideal for people with a mix of devices, such as a Windows PC and an iPhone, or a Mac and an Android device.