In the earlier article, we saw how to read the Data Source. In this article, let us see how to update the Custom Data Source using PowerShell Script.
The below script is self-explanatory.
# This script will update the data source info for all the reports in a document library
##================================================================================================
## Description :
#This script will update the 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-$LogTimeTranscript.rtf"
$ProgressFile = ".Logs-$LogTimeProgress.rtf"
try{
stop-transcript|out-null
}
catch [System.InvalidOperationException]{}
start-transcript $TranscriptFile
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
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 Proxy
$targeturl="$($context.Web.Url)/_vti_bin/ReportServer/ReportService2010.asmx";
$targetProxy = New-WebServiceProxy -Uri $targeturl -UseDefaultCredential;
$targetNS = $targetProxy.GetType().Namespace
$targetDataType = $targetNS + '.DataSourceDefinition'
# 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.nee.com' + $file.ServerRelativeUrl
$dataSources = $targetProxy.GetItemDataSources($fullURL)
Write-Host "Count : " $dataSources.Count
foreach($dataSource in $dataSources)
{
Write-Host $dataSource.Name
Write-Host $dataSource.Item.ConnectString
#The below line is where actual updation is happening
$dataSource.item.ConnectString = "Updated Connection String Value"
$targetProxy.SetItemDataSources($fullURL, $dataSource)
}
}
}
}
}
}
try{
stop-transcript|out-null
}
catch [System.InvalidOperationException]{}
Happy Coding,
Sathish Nadarajan.
Leave a comment