Reza Alirezaei’s Blog

As I promised last week in this blog post, the first chance I get , I would craft up another POC using PowerShell that takes care of deploying SSRS reports and data sources in bulk to SharePoint.Here you go:

You can download the script here and below are the steps to get this script to work:

1) Create a folder and throw the script there.

2) All right! now you need to compile the SSRS Porxy endpoint for SharePoint integrated mode (ReportService2006.asmx) and build the  ReportService2006.dll. There are couple ways to do this (like this), but most likely the easiest way is by using Visual Studio command prompt.This is where wsdl.exe comes handy.Just as FYI, Wsdl.exe parses ReportService2006.asmx schema and its WSDL file and generates a .NET class (ReportService2006.cs). Since the schema of ReportService2006.asmx won’t change until the next version (I hope so!), you don’t need to repeat this step anymore. Open Visual Studio command prompt and type:

wsdl http://myserver/_vti_bin/ReportServer/ReportService2006.asmx 

Now, invoke and point the C# compiler (csc.exe) to the generated class and create the DLL (in your active directory):

 csc /t:library ReportService2006.cs

In the source code, this dll is used to communicate with the endpoint.

• If you don’t know what the hell I am talking about , I kindly ask you to read this post to know what I mean by SSRS proxy endpoint.Thanx!
• If you are lazy , you can download the dll right from here. I’ve already generated the dll.

3) Dump  the ReportService2006.dll in the same folder as the script.

4) Throw all of your reports and data source in the same folder.

Actually, this Powershell script (unlike the feature approach) is not bound to one shared data source.  I read the data souce from the rdl file, so as long as the data source is physically present in the same folder,script works just fine. Obviously , this is just another POC ;therefore it comes with no exception handling!

5) Open the PowerShell command line , browse to your folder  and type in the following command:

DeploySSRSInteg.ps1 -targetRptLib “http://myserver/myrptlib” -targetDCL “http://myserver/myDCL” -sourceFolder “C:\SSRSDeployment ” -rsProxyEndpt “http://myserver/_vti_bin/ReportServer/ReportService2006.asmx”

6) Once you hit the enter , you should be able to see a traffic light popping into your screen 🙂 (just kidding!). Here is the execution screenshot:

7) And here are all your data sources and reports deployed for you. PoSh rocks baby!

Data sources are converted:

Reports are published & its data source is nicely referenced:

‘8) As always, this is just a POC and by no means production ready , so use it at your own risk!

With all the issues IIS caused in the previous versions of SSRS, specially to configure it to interact with other applications (such as SharePoint) hosted in IIS , in SSRS 2008, Microsoft made a major architectural shift to move toward to a *real* Single Service Architecture that would eliminate dependency on IIS.

You no longer require having IIS installed on the RS box and, even nicer; you don’t have to install SQL on that RS box! Well, let me rephrase what I just said above. SSRS doesn’t require IIS to function, but this only applies when you configure it in native mode. In integrated mode and since you have to join the SSRS machine to the farm, you have to install SharePoint WFE components on RS machine and this means dependency on IIS, but again that’s the dependency SharePoint has on IIS , not SSRS!

This shifting architectural decision took care of two very serious issues bugging customers constantly:

1. SSRS trying to take over port 80 which was in control of SharePoint and subsequently throwing an unrelated exception that tipped many people over the edge when configuring SSRS in integrated mode.
2. Hosting IIS on the database server and potentially exposing security threats.

In the previous version of SSRS, there were two services (Web Service and Windows Service) that worked hand-to-hand and collaborated (Collab sounds familiar eh? 😉 ) to execute the reports, but in the new version they rearchitected and abstracted everything to a SINGLE service – SSRS Windows service. Obviously, WMI APIs are still available for configuration tools, third party apps and your enterprise deployment scripting fun, but Windows service is just serving as the main entry into all of the background and foreground services SSRS offers!

Well, finding a suitable host to support the shifting decision was not that difficult. They just shoved everything into the common architectural layer as with SQL Server uses, but the idea of hosting ASP.NET inside SQL CLR inside SSRS service process was extremely intuitive. I was like WOW when I first saw it  (my jar hitting the floor kind a) – SSRS team literally came up with their own mini IIS with many similar characteristics. Wait! It doesn’t mean that you have another web server hosted in SQL CLR. It’s just there for them to serve incoming HTTP requests using SQL Server networking interface and again mainly for departing from IIS land.

Why am I using the word mini IIS here is because, first SSRS uses the same underlying network layer as IIS 6 and higher uses, built right into the operating system– a.k.a HTTP.SYS layer. Secondly, SSRS 2008 has all the IIS components it requires to function without having any dependencies on IIS. Moving away from IIS, called for two major actions to be taken care of by SSRS team:

1. Disabling some of the functionalities IIS handles which there are no equivalent in SSRS 2008 , such as anonymous, client certificate and ISAPI crap. (I have allergy to ISAPI – sorry!)
2. Enabling some of the functionalities that IIS used to handle for SSRS before. Things such as ability to add virtual directory, port, SSL certificate, W3C compliant logging mechanism and so on so forth.

A lot of new IIS-Like settings are configured by Reporting Service Configuration Manager (picture below) , a tool that is significantly changed since the previous version. In this tool, Web Service URL tab is a just a new (and a very important) tab that offers creating Virtual directory , assigning ports and applying SSL certificates, but there is this important drop down that has the same behavior as IIS: IP Address.

As you can tell, the recommended option is All Assigned. This means that any HTTP requests that come in the specified port (TCP Port text box) and uses the specified URL reservation and virtual directory name (Virtual Directory text box) should be served by Reporting Services. So if you see something like http://+:80/ReportServer , the wildcard sign (+) indicates that you are using port 80 and All assigned when configuring the URL reservations settings for your report server.

On the other hand All Unassigned indicates that if there are no other listeners (services) registered for a specific URL reservation , on this port , for this virtual directory then give me (not me , I mean SSRS 🙂 ) the request. As you can see it fully supports IPv4 and wild card IPv6 addresses  and exactly like IIS you can load balance the incoming requests if your server happens to be using more than one NIC cards. Very nice, eh?

Let’s have some fun here:

Setup a SharePoint Web application on port 80 (let’s say http://mossdev) and then in configuration tool choose the same port for SSRS – for example http://mossdev/reportserver , with All Assigned option selected. If you note you can share the port with IIS hosted Web app. Then create a team subsite in SharePoint called ReportServer (http://mossdev/reportserver) – Once the proviosning of the site is finished, you will be redirected to SSRS – I laughed 🙂 !Change the SSRS virtual directory name or port number, issue is fixed. As you can see, understanding URL reservation is extremely important.

If you would like to see all of the URL reservations currently in place on your server , just be aware that HTTP.SYS comes with a set of command line utils. For example,  by typing “netsh http show urlacl” command , you would get all of the URL reservations echoed for ya. If you note in this particular case I am using a different port (Heck!It can even be a different URL!) for Report Manager and Report Sever.

If any one of you understand what those security descriptor strings (SDDL) mean , please drop me a line and educate me as well 😉

These URL reservation must mach the URL Resevation node in the RS configuration file as shown below, otherwise when you start the Windows service , there are going to be some inconsistencies in services SSRS offer:

See if you read all the documentation about SSRS 2008 integrated mod, they pretty much ask you to create the reports in BIDS and go through the publishing steps or simply upload your Reporting services artifacts to the designated document libraries. This is nice and easy, but sucks big time!

First off , no offense , but the majority of those who deploy have absolutely no clue how to use BIDS! Secondly BIDS is not necessarily available on the production servers. Thirdly, there are many scary clients out there who don’t entertain anything except full automation of your development bits including your reports, data models, data source and etc. All this calls for an automated solution that takes care of deploying your reports and all of their dependencies.

Well, we have a problem though! While you can publish report definitions (.rdl) and report models (.smdl) to a SharePoint library, this does NOT apply to data source (.rds) files! That’s exactly what design tools (i.e. BIDS) do under the hood! During publication, design tools create a new .rsds file from the original .rds file and publish that file instead. They literally convert the .rds file to its .rsds counterpart which is pretty contains same content but in different schema! This process very much resembles to what InfoPath publishing wizard does by turning UDC files into UDCX files for browser-enabled forms.

Truth to be told, if you want to fully automate your deployment, you should write your own converter and perform the deployment by utilizing SharePoint feature framework and SSRS proxy endpoint (ReportService2006.asmx). I am not sure how difficult it was for the SSRS team to provide another web method that takes a .rds file and publish it to a SharePoint document library!! The functionality is there and is used by design tools, right? They just didn’t expose it to us!

Okay, I just wrapped up a POC that demonstrate how you can accomplish this. You can download the code here.

1. Craft up a project and include the shared data source and all of your reports like below:

BTW, I am using WSPBuilder for this project.

2. In feature.xml, put in the following CAML code snippet:

As you can tell, in the feature manifest, I am referencing the RsProducts.rdl and DtSourceNorthwind.rds files as ElementFile inside ElementManifests node.In this blog post I am assuming that each feature pack contains multiple reports,but share one data source. If you have other reports which don’t share data source, just create more features. Who cares? Too many features?!! Well, you can hide them all and use a hub feature that when gets activated, activates all of the child features; so does the deactivation. No worries!

One more thing about the feature above. Since I am using Data Connection Library and Report Library to host my SSRS artifacts, there is this activation dependency to ensure that enterprise feature is already turned on.

Let’s move on and have a look at the code in the feature receiver:

[CSharp]
public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
SPWeb web = properties.Feature.Parent as SPWeb;
ReportingService2006 rs06 = new ReportingService2006();
rs06.Url = web.Url + “/_vti_bin/ReportServer/ReportService2006.asmx”;
rs06.Credentials = System.Net.CredentialCache.DefaultCredentials;
…
}
[/CSharp]

In the FeatureActivated method, I first initialize the SSRS Proxy endpoint which has already been nicely virtualized and context aware by SSRS Add-in for me. Security context under which this Web service call gets executed is determined by how you set up SSRS authenitcation mode in the Central Administration site. Read my post here.

[CSharp]
string featurePath = string.Format(@”{0}\FEATURES\{1}\”, SPUtility.GetGenericSetupPath(“Template”), @”Top20Products”);
string[] reportPaths = Directory.GetFiles(featurePath, “*.rdl”);
string[] dataSourcePath  = Directory.GetFiles(featurePath, “*.rds”);
if(dataSourcePath.Length != 1)
throw new Exception(“Oops!Only one shared datasource per feature pack or there is no datasource”);
[/CSharp]

So the plan is I can simply copy reports and their shared data source over to the feature folder so when feature gets activated I can call ReportService2006.asmx to publish reports, create the data source and finally fix up the data source reference in the deployed reports. Since the report and its data source is already deployed to the feature folder , I can simply get a reference to the files on the WFE server.

[CSharp]
XmlNode extensionNode = null;
XmlNode connectStringNode = null;
XmlNode rptDataSourceNameNode = null;

fileName = Path.GetFileName(dataSourcePath[0]).Replace(“rds”, “rsds”);
stream = File.OpenRead(dataSourcePath[0]);
XmlDocument dsDOM = new XmlDocument();
dsDOM.Load(stream);
stream.Close();
rptDataSourceNameNode = dsDOM.SelectSingleNode(“RptDataSource/Name”);
XmlNodeList connectionPropertiesNodes = dsDOM.SelectNodes(“RptDataSource/ConnectionProperties”);

foreach (XmlNode node in connectionPropertiesNodes)
{
extensionNode = node.SelectSingleNode(“Extension”);
connectStringNode = node.SelectSingleNode(“ConnectString”);
}

DataSourceDefinition dsDefinition = new DataSourceDefinition();
DataSource dSource = new DataSource();
dSource.Item = dsDefinition;
dsDefinition.Extension = extensionNode.InnerText;
dsDefinition.ConnectString = connectStringNode.InnerText;
dsDefinition.ImpersonateUserSpecified = true;
dsDefinition.Prompt = null;
dsDefinition.WindowsCredentials = true;
dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated;
dSource.Name = rptDataSourceNameNode.InnerText;
string parentDataSourceLibrary = “http://mossdev/DemoDCL”;
string fullDSUrl = string.Concat(parentDataSourceLibrary,”/”, fileName);
rs06.CreateDataSource(fileName, parentDataSourceLibrary, true, dsDefinition, null);
[/CSharp]

As you can tell, in the receiver class I am loading the .rds file into an XMLDocument object which I can then traverse to programmatically create the equivalent .rsds data source. Please be noted that for simplicity sake, I am just converting a simple data source (look st the source code), but be my guest if you’d like to deserialize .rds data source into a full-blown business object to cover more complex data source types. I am just trying  to keep the POC focused on the topic and simple!

Quite frankly, I don’t care about setting data sources properly during deployment. You can just create dummy data sources, reference those in your reports and when everything gets deployed, just go to the Data Connection Library and fix up the data sources. As part of the integration, you have the ability to set data source properties right from SharePoint UI. Plus , you should always be using shared data sources as much as possible so supposedly there shouldn’t be crazy number of data sources in your deployment.

I also hardcoded the parentDataSourceLibrary path that points to a Data Connection Library. You should either programmatically create the Report and Data Connection library in your feature receivers or use the same ( or another) feature to create these doc libs using CAML. I assumed that lists are already there. what a lazy a$$ I am 😉

[CSharp]

Warning[] warnings = null;
foreach (string rptPath in reportPaths)
{
fileName = Path.GetFileName(rptPath);
stream = File.OpenRead(rptPath);
Byte[] definition = new Byte[stream.Length];
stream.Read(definition, 0, (int)stream.Length);
stream.Close();
string parentReportLibrary = “http://mossdev/DemoReports”;
string fullReportURL = string.Concat(parentReportLibrary, “/”, fileName);
CatalogItem item = rs06.CreateReport(fileName, parentReportLibrary, true, definition, null, out warnings);
foreach (Warning warning in warnings)
{
//Proceed with logging the warnings
}
DataSource[] catalogItemDtSrcs = rs06.GetItemDataSources(fullReportURL);
DataSourceReference reference = new DataSourceReference();
DataSource dsNew = new DataSource();
reference.Reference = fullDSUrl;
dsNew = catalogItemDtSrcs[0];
dsNew.Item = reference;
rs06.SetItemDataSources(fullReportURL, catalogItemDtSrcs);
}
[/CSharp]

At the end we just publish the reports again to a hardcoded Report library named “DemoReports ” and fix up the wrong data source references. That’s basically it. just Build & Deploy the project. Upon successful deploy , you should see a Web scoped feature that when gets activated , injects the data source into a Data Connection Library called DemoDCL and the report to a Report library called DemoReports.

*The source code for the sample we did in this blog post can be downloaded here.

*If I get a chance next Weekend , I will craft up a PowerShell script which does pretty much the same thing  but without utilizing the feature framework. I will keep you posted here!

1/2 a great rest of your weekend 😉

Microsoft just pushed out a new timbombed virtual machine to the MS download site. You can download it from here. The new image is timbombed for March 2010 which gives you the longest period MS has ever allowed activated machines to operate before they get timebombed.

If you want to learn more about timebomb technique read my posts here, here and here.

<DoNotFeelLikeReading?> In short, these machines are protected by two anti-piracy measures – timebomb and activation. Activation kicks in after the trial is over (30 days) and timebomb destrys the machine at a certain date no matter what (in this case it’s March 2010) . If you opt in on activating the virtual machine using your non-VL MSDN key , you can enjoy SharePoint and its complementray technologies for the next 17 months</DoNotFeelLikeReading?>

Download it today, get yourself familiar with SharePoint and join the wave! ShrePoint Nation!

Although when installing SSRS 2008, you get an option to install in “integrated mode”, “native” or “Install, but configure later”, there is always this question that if I have setup my report server in the native mode how easy it would be to :

1. Switch my report server to the integrated mode
   
2. Switch my report server databases to the integrated mode
   

Okay, let me take a pause and answer the second question first because it is easier! You cannot shift your databases from native to integrated mode or vice versa. Report server databases contain mode-specific data and currently there is no process that I am aware of, to convert them over to the other mode. This means that if you’ve setup your report server in native mode and you’ve published your reports, once you make the switch, you ought to publish everything again to the new report server. Period!

However, the first question calls for a more visual kind of answer; therefore the main focus of this blog post. I have categorized the steps you require to take into two sections.

I) SSRS configuration Steps:

1) Fire Reporting Services Configuration Manager.

2) Connect to the right instance of the report server that you want to convert.

3) On the Service Account tab, choose the right service account under which SSRS windows service must be executed. There is this fantastic table here that provides service account recommendations for different deployment scenarios. Read up for yourself!

4) On the Web Service URL tab, change the tcp port, apply SSL if required. Don’t forget to click on Apply button if you change anything on this screen. Here is what you should see in the result pane:

Read more…