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.
Leave a comment