none
Connecting SSRS Reports to Shared DataSources in SharePoint 2010

    Question

  • Hi,

    In our current setup, we are integrating SSRS 2012 with SharePoint 2010 . We are having around 500 reports that we will be deploying (Using SQL Server Data Tools) to multiple folders with in a reports library named "Reports".

    We are having 4 (Shared) data sources for all those 500 reports and all the data sources will all be deployed in a separate data connections library (Using SQL Server Data Tools) named "Data Connections" where we will be deploying the .rsds file.

    Now, In order to make the reports work, We need to connect all the 500 reports to their respective shared data sources . For this we need to go to the report  and from the drop down , Select Manage Data Sources and click on the existing data source name (DataSource1 in this case) and choose Shared datasurce and point to my datasources in the "Data Connections"library.

    But it is practically impossible to do go into each report and change it manually. It would a killing task that would eat up one whole day or may be even more.

    Is there any alternate way to do this? Are there any PowerShell scripts to connect these reports to the SharedDataSources ? 

    I know how to do this using a power shell script in case of Report Manager, But I have no idea how to manipulate the datasource conenctions in SharePoint integrated mode.

    Any help would be greatly appreciated!!

    Thanks in Advance,

    Karteek.

    Tuesday, May 15, 2012 11:16 PM

Answers

  • Looks like you should be setting your data source reference to $newDataSourceName, instead of $newDataSourcePath:

    $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

    $reports = $ssrs.ListChildren($reportFolderPath, $false)

    $reports | ForEach-Object {
             $reportPath = $_.path
             Write-Host "Report: " $reportPath
             $dataSources = $ssrs.GetItemDataSources($reportPath)
             $dataSources | ForEach-Object {
                       $proxyNamespace = $_.GetType().Namespace
                       $myDataSource = New-Object ("$proxyNamespace.DataSource")
                       $myDataSource.Name = $newDataSourceName
                       $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
                       $myDataSource.Item.Reference = $newDataSourceName

                       $_.item = $myDataSource.Item

                       $ssrs.SetItemDataSources($reportPath, $_)

                       Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)"
                       }

             Write-Host "------------------------" 
             }

    • Marked as answer by gspkarteek Wednesday, May 16, 2012 11:01 PM
    Wednesday, May 16, 2012 10:10 PM

All replies

  • You could consider using the SOAP APIs to set the data sources on your reports.  The SetItemDataSources API can be used to do this:
    http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.setitemdatasources.aspx

    Here is an example of this being done in a PowerShell script.  In your case, the URL to point to would be something like http://<SPSite>/_vti_bin/ReportServer/ReportService2010.asmx:
    http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell

    Wednesday, May 16, 2012 7:10 AM
  • Thanks Tristan Fernando for the links you sent.

    As I am still trying to understand the first link that you sent, I tried to prepare a PowerShell script based on the 2nd link that you have suggested.

    Here is the script that I am trying, I couldn't make it work though. Any Suggestions!!?

    #--------------------------------------------------------------------------- Power Shell Script------------------------------------------------------------------

    #Clear Screen
    cls;

    #Set variables:
    $reportserver = "SharePoint";
    $newDataSourcePath = "http://SharePoint/Data%20Connections/"
    $newDataSourceName = "http://SharePoint/Data%20Connections/DataSource1.rsds";
    $reportFolderPath = "http://SharePoint/reports/SSRSReports"
    $url = "http://$($reportserver)/_vti_bin/ReportServer/ReportService2010.asmx"
    #------------------------------------------------------------------------

    $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

    $reports = $ssrs.ListChildren($reportFolderPath, $false)

    $reports | ForEach-Object {
             $reportPath = $_.path
             Write-Host "Report: " $reportPath
             $dataSources = $ssrs.GetItemDataSources($reportPath)
             $dataSources | ForEach-Object {
                       $proxyNamespace = $_.GetType().Namespace
                       $myDataSource = New-Object ("$proxyNamespace.DataSource")
                       $myDataSource.Name = $newDataSourceName
                       $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
                       $myDataSource.Item.Reference = $newDataSourcePath

                       $_.item = $myDataSource.Item

                       $ssrs.SetItemDataSources($reportPath, $_)

                       Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)"
                       }

             Write-Host "------------------------" 
             }

    #-----------------------------------------------------------------------------------------------------------------------------------------------------------------

    There is one Report (report1.rdl) in the SSRSReports folder and I am getting the following error: 

    Report:  http://sharepoint/Reports/SSRSReports/report1.rdl
    Exception calling "SetItemDataSources" with "2" argument(s): "The operation you are attempting on item 'http://sharepoint
    /Data Connections' is not allowed for this item type. ---> Microsoft.ReportingServices.Diagnostics.Utilities.WrongItemT
    ypeException: The operation you are attempting on item 'http://sharepoint/Data Connections' is not allowed for this item
    type."
    At line:12 char:44
    +                    $ssrs.SetItemDataSources <<<< ($reportPath, $_)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException

    **Note: The above script(with some changes) works for me when i run it on a report server but i don't know why it doesn't work for SharePoint.

    Thank You,

    Karteek.

    Wednesday, May 16, 2012 9:19 PM
  • Looks like you should be setting your data source reference to $newDataSourceName, instead of $newDataSourcePath:

    $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential

    $reports = $ssrs.ListChildren($reportFolderPath, $false)

    $reports | ForEach-Object {
             $reportPath = $_.path
             Write-Host "Report: " $reportPath
             $dataSources = $ssrs.GetItemDataSources($reportPath)
             $dataSources | ForEach-Object {
                       $proxyNamespace = $_.GetType().Namespace
                       $myDataSource = New-Object ("$proxyNamespace.DataSource")
                       $myDataSource.Name = $newDataSourceName
                       $myDataSource.Item = New-Object ("$proxyNamespace.DataSourceReference")
                       $myDataSource.Item.Reference = $newDataSourceName

                       $_.item = $myDataSource.Item

                       $ssrs.SetItemDataSources($reportPath, $_)

                       Write-Host "Report's DataSource Reference ($($_.Name)): $($_.Item.Reference)"
                       }

             Write-Host "------------------------" 
             }

    • Marked as answer by gspkarteek Wednesday, May 16, 2012 11:01 PM
    Wednesday, May 16, 2012 10:10 PM
  • oh man Tristan Fernando  YOU ARE A ROCKSTAR!!!

    Thanks a lot. Hope this helps lots of people..

    Wednesday, May 16, 2012 11:03 PM