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

Sathish Nadarajan
 
Solution Architect
January 9, 2018
 
Rate this article
 
Views
3111

 

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.

Category : CSOM, PowerShell, SharePoint

Author Info

Sathish Nadarajan
 
Solution Architect
 
Rate this article
 
Sathish is a Microsoft MVP for SharePoint (Office Servers and Services) having 15+ years of experience in Microsoft Technologies. He holds a Masters Degree in Computer Aided Design and Business ...read more
 

Leave a comment