PowerShell:Deploying SSRS Reports in Integrated Mode
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!
Where are the functions CreateDataSource.rss, CreateFolder.rss, and UploadReport.rss located?
Whats the magic to getting this to work in native mode? I recieve an error indicating the call cannot be made in native mode.
Exception calling “CreateReport” with “6” argument(s): “System.Web.Services.Protocols.SoapException: This operation is
not supported on a report server that runs in native mode.
@bee
At first, you should use ReportService2005.asmx for Native mode SSRS.
I have not read the code deeply, but surely there should be more modifications for this to work on native.
Hi,
I am getting this error while trying to run the deploy command from PowerShell
The term ‘DeploySSRSInteg.ps1’ is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
Am I doing something wrong here?
Hi,
I managed to solve the previous problem. However, now I am getting this error while executing the script from PowerShell-
Exception calling “CreateDataSource” with “5” argument(s)
I am using Reoprting Services 2008 in SharePoint integrated mode with MOSS 2007. Hope the version is not a problem here.
Hi,
Has anyone tried to script the “addition of a report server to the integration”?
My problem is the following: I do not have enough to justify a NLB etc. to guarantee high availability for the report server, but I can create two RS pointing at the same SSRS config DB (the DB is clustered). If the “main” RS fails, I can bring it down from SharePoint and bring up the other, but it would be simpler doing it through a script…
Any help would be appreciated.
Thanks Lou
How do I get a Shared Dataset to be included in this script. Seems to work for RSD and RDL files, but not sure I understand fully how to manipulate the XML for the DataSets.
Looks like I need a new method to handle this, but not sure how.
I copied the Report function and just changed the XML read to use the following: $localDSName = $rsdXml.SharedDataSet.DataSetName.Query.DataSourceReference
but to no avail.
Any assistance would be greatly appreciated.
@Steven_Inter
Steven,
I am also looking for a solution to use “Shared Dataset”. Did you get any solution so far on the same?
@Steven_Inter
Steven,
Not sure if you are still looking for a solution for “Shared Dataset”. It works just fine if you add extensions to datasource and dataset references in RDL (Report) and RSD (Dataset) files.
For example, if “SDS” is your datasource, in the RSD file datasource reference looks like the following:
SDS
Change this to the following:
SDS.rsds
Similarly add extensions to datasource and dataset references in RDL file as shown below:
SDS.rsds
SDSet.rsd
Thanks Reza.
Very useful.
Cheers,
Claud
Hi Reza,
I am newbie for MS BI Stack and Powershell.
I am working on the script provided for deploying datasets and datasources. but the problem is I could not find ReportService2010.asmx or ReportService2006.asmx to set as my proxyEndpoint in my ReportServer directory.
Could you please let me know how to deal with this.
Hi Reza,
I want to get the same stuff working for sharepoint 2010.Can you please let me know how to go about it ?