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

In this article, let us see how to read the Data Source Information of RDL/RDLX file in SharePoint using PowerShell Script. This will fit for SP2010, 2013, 2016 as well. Only the Client DLLs should be updated accordingly. The core logic remains the same.

 ################# Set the Current Path as Execution Path ####################
 
 $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
 Set-Location $scriptBase
 
 ############# set the Error Preference ################
 
 $ErrorActionPreference = "SilentlyContinue"
 
 
 function AddCSOM(){
      #Load SharePoint client dlls 
      $a = [System.Reflection.Assembly]::LoadFile(    "$scriptBaseClientLibrariesMicrosoft.SharePoint.Client.dll") 
      $ar = [System.Reflection.Assembly]::LoadFile(    "$scriptBaseClientLibrariesMicrosoft.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
 
 
 #Create and add the headers on the CSV File
 Add-Content $DataSource_CSV_Path "FileURL, DataSourceName, DataSourceURL, ConnectString, DataSourceType"
 
 
 
     
     
         
     $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 Proxy
     $targeturl="$($context.Web.Url)/_vti_bin/ReportServer/ReportService2010.asmx";
     $targetProxy = New-WebServiceProxy -Uri  $targeturl -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 = 'http://Mysitecollection' + $file.ServerRelativeUrl
                         $dataSources = $SSRS.GetItemDataSources($fullURL); 
                         Write-Host "RDL file Count : "  $dataSources.Count
 
                         if ($datasources.count -gt 0)
                         { 
                         foreach ($DataSource in $dataSources){ 
                          Write-Host "$($DataSource.name) : $($DataSource.item.reference)"; 
                           if($DataSource.item.reference)
                           {
                             $DataSourceType="Shared"
                           }
                           if($DataSource.Item.ConnectString)
                           {
                             $DataSourceType="Custom"
                           }
                           Add-Content -Path $DataSource_CSV_Path -Value ($fullURL+','+$DataSource.name +','+$DataSource.Item.Reference+','+$DataSource.Item.ConnectString+','+$DataSourceType)
                         } 
                        
                        } 
         
          }
                    
        }
      }
     }
        
   }
 

Happy Coding,

Sathish Nadarajan.