How to Update the Shared Data Source Information of a RDL RDLX files in SharePoint using PowerShell Script


Sathish Nadarajan
SharePoint MVP
Published On :   22 Jan 2018
Visit Count
Today :  7    Total :   491
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

SharePoint Office 365 Tool
Simple & Powerful Tool for Migration, Security & Reporting. Free Trial


In the earlier article, we saw how to read the Data Source and Update the Custom Data Sources. In this article, let us see how to update the Shared Data Source using PowerShell Script.

The below script is self-explanatory.

# This script will update the Shared data source info for all the reports in a document library

 # This script will update the Shared data source info for all the reports in a document library 
 
 ##================================================================================================
 ## Description	: 
     #This script will update the Shared data source info for all the reports in a document library 
  
 ## Author		: Sathish Nadarajan
 ## Date			:  
 ##================================================================================================
 
 # ============================================ Setup Input Paths ================================= 
 
 cls
 
  
 $Host.UI.RawUI.WindowTitle = "-- Update Data Source for Reports Library --"
 
 $StartDate = Get-Date
 Write-Host -ForegroundColor White "------------------------------------"
 Write-Host -ForegroundColor White "| Update the reports file with datasource url |"
 Write-Host -ForegroundColor White "| Started on: $StartDate |"
 Write-Host -ForegroundColor White "------------------------------------"
 
 $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
 
 ################# Set the Current Path as Execution Path ####################
 
 $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
 Set-Location $scriptBase
 
 ############# set the Error Preference ################
 
 $ErrorActionPreference = "SilentlyContinue"
 
 
 # Create Log File Folder
 if(!(TEST-PATH ".\Logs-$LogTime")) 
 {
    NEW-ITEM ".\Logs-$LogTime" -type Directory
 }
 
 
 
  # Assign the Log and Progress Files
 $TranscriptFile = ".\Logs-$LogTime\GetSiteInfo.Transcript.rtf"
 $ProgressFile = ".\Logs-$LogTime\GetSiteInfo.Progress.rtf"
 
 try{
   stop-transcript|out-null
 }
 catch [System.InvalidOperationException]{}
 
 start-transcript $TranscriptFile
 
 
 function AddCSOM(){
      #Load SharePoint client dlls 
      $a = [System.Reflection.Assembly]::LoadFile(    "$scriptBase\ClientLibraries\Microsoft.SharePoint.Client.dll") 
      $ar = [System.Reflection.Assembly]::LoadFile(    "$scriptBase\ClientLibraries\Microsoft.SharePoint.Client.Runtime.dll") 
      
      if( !$a ){
          $a = [System.Reflection.Assembly]::LoadWithPartialName(        "Microsoft.SharePoint.Client")
      }
      if( !$ar ){
          $ar = [System.Reflection.Assembly]::LoadWithPartialName(        "Microsoft.SharePoint.Client.Runtime")
      }
      
      if( !$a -or !$ar ){
          throw         "Could not load Microsoft.SharePoint.Client.dll or Microsoft.SharePoint.Client.Runtime.dll"
      }
      
      
      #Add overload to the client context.
      #Define new load method without type argument
      $csharp =     "
       using Microsoft.SharePoint.Client;
       namespace SharepointClient
       {
           public class PSClientContext: ClientContext
           {
               public PSClientContext(string siteUrl)
                   : base(siteUrl)
               {
               }
               // need a plain Load method here, the base method is a generic method
               // which isn't supported in PowerShell.
               public void Load(ClientObject objectToLoad)
               {
                   base.Load(objectToLoad);
               }
           }
       }"
      
      $assemblies = @( $a.FullName, $ar.FullName,     "System.Core")
      #Add dynamic type to the PowerShell runspace
      Add-Type -TypeDefinition $csharp -ReferencedAssemblies $assemblies
 }
 
 
 AddCSOM
 
 $credentials = Get-Credential
 
  
 
 
 cls
 
  
     
     Write-Host "Processing the Site : " "https://mysitecollection" -ForeGroundColor Yellow
         
     $context = New-Object SharepointClient.PSClientContext("https://mysitecollection")
    
      
     $context.Credentials = $credentials
     
     #Load the basic information about the web and site
     $context.Load($context.Web)
     $context.Load($context.Site)
     $context.Load($context.Web.Lists)
     $context.ExecuteQuery()
 
     #SSRS
     $SSRSurl = "$($context.Web.Url)/_vti_bin/ReportServer/ReportService2010.asmx"; 
     $SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential; 
 
     $($ssrs.url); 
 
     # Iterate through the Lists
     foreach ($list in $context.Web.Lists) 
     {
         #Load the information about the List
         $context.Load($list)
         $context.Load($list.BaseType)
         $context.Load($list.Items)
         $context.ExecuteQuery()
         
         
         # validate for Document Library
         if ($list.BaseType -eq “DocumentLibrary”) 
         {
           
             $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
             $camlQuery.ViewXml ="<View Scope='RecursiveAll' />";
             $allItems=$list.GetItems($camlQuery)
             $context.Load($allItems)
             $context.ExecuteQuery()
            
             foreach($item in $allItems)
             {
                 if($item.FileSystemObjectType -eq "File")
                 {
                     $file = $item.File
                     $fItem = $file.ListItemAllFields
                     $context.Load($file)
                     $context.Load($fItem)
                                    
                     $context.ExecuteQuery()    
                      
                      #Read .rdl files available in the library  
                       
                       if($file.Name.ToLower().Contains(".rdl") -or $file.Name.ToLower().Contains("rdlx"))
                        
                        {                   
                         $fullURL = 'https://mysitecollection' + $file.ServerRelativeUrl
                         $dataSources = $SSRS.GetItemDataSources($fullURL); 
                         Write-Host "Count : "  $dataSources.Count
 
                         if ($datasources.count -gt 0)
                         { 
                             for ($i = 0; $i -lt $dataSources.count; $i++) 
                             { 
                                    
                                   #The below Lines will update the Shared Data  Source
                                   $proxyNamespace = $DataSources[$i].GetType().Namespace; 
                                   $DataSources[$i].Item = New-Object ("$proxyNamespace.DataSourceReference"); 
                                   $DataSources[$i].Item.Reference = "https://mysitecollection/documentlibrary/RSDSFile.rsds";
                                   $SSRS.SetItemDataSources($fullURL, $DataSources[$i]) 
                                   write-output "Done";
                                     
                                  
 
                 }
                  
             }
           
         
          }
                    
        }
      }
     }
        
   }
        
    
  
 
  try{
   stop-transcript|out-null
 }
 catch [System.InvalidOperationException]{}   
 

Happy Coding,

Sathish Nadarajan.

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

KWizCom Forms App