Home > MOSS 2007, SSRS > PowerShell:Deploying SSRS Reports in Integrated Mode

PowerShell:Deploying SSRS Reports in Integrated Mode

October 22nd, 2008 Leave a comment Go to comments

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:

donload.pngYou 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:
DCLProof

Reports are published & its data source is nicely referenced:
ReportProof

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

Categories: MOSS 2007, SSRS Tags:
  1. jv
    January 18th, 2010 at 14:43 | #1

    Where are the functions CreateDataSource.rss, CreateFolder.rss, and UploadReport.rss located?

  2. bee
    March 2nd, 2010 at 14:53 | #2

    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.

  3. Luis
    July 8th, 2010 at 16:18 | #3

    @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.

  4. Amit
    August 25th, 2010 at 08:19 | #4

    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?

  5. Amit
    August 27th, 2010 at 08:24 | #5

    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.

  6. Lou
    September 19th, 2010 at 11:30 | #6

    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

  7. Steven_Inter
    February 1st, 2011 at 22:03 | #7

    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.

  8. Vasu
    April 13th, 2011 at 11:42 | #8

    @Steven_Inter
    Steven,
    I am also looking for a solution to use “Shared Dataset”. Did you get any solution so far on the same?

  9. Vasu
    April 22nd, 2011 at 01:18 | #9

    @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

  10. Claudio
    June 2nd, 2011 at 03:20 | #10

    Thanks Reza.

    Very useful.

    Cheers,
    Claud

  11. September 22nd, 2011 at 05:31 | #11

    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.

  12. Kumar Mayank
    October 25th, 2011 at 01:15 | #12

    Hi Reza,

    I want to get the same stuff working for sharepoint 2010.Can you please let me know how to go about it ?

You must be logged in to post a comment.