Update url and server references in SharePoint content can be quite challenging when you a lot of your Excel Services Reports, Reporting Services Reports and Data Connections (ODC, PPSDC and RSDS files). This post assumes you have migrated to a new SharePoint server with new hostname/dns name and have new database and analysis services servers which are used as source for reports within your SharePoint Business Intelligence portal. The task of manually go through all report and update database and server references can be a quite tedious task. To our rescue: PowerShell! I have put together a PowerShell script which can update url and server references in SharePoint content described below:

Excel Services Reports (Viewed either in Excel or in-browser in SharePoint or with Excel Services)
- External Data Connection strings
- Excel External Data Connection file reference (ODC files for cubes)
Excel Web Access Web Parts (referencing url of Excel Report in Document Library)
- Excel Workbook Url
Reporting Services
- Report Data Connection Url
Excel PowerView/PowerPivot Reports
- This will not be covered in this post but see for some ideas of how it can be done:
  http://www.sqlservercentral.com/blogs/data-adventures/2015/06/18/updating-powerpivot-model-and-powerview-report-with-powershell/

Instructions

To control which action(s) the script should do there are a few variables in the beginning of the script that needs to be configured correctly. You will also have to configure how your server names and url’s should be replaced. The script only updates references/text in Office Open Document format Excel (XLSX) files. Hence if you have old XLS files you will first need to download them to disk. Then convert them to XLSX and upload them to your SharePoint portal again. This process is also partly automated using multiple scripts. The SP_UpdateUrlRef script can download the XLS files and upload the converted XLSX files. The Convert_XLS script can automate the conversion but has to be run on a computer with Microsoft Office 2013 (only version I have tested).

PowerShell script execution steps

Download powerShell script zip package (includes update script (SP_UpdateUrlRef) and XLS conversion script (Convert_XLS) and DocumentFormat.OpenXml.dll)
Open SP_UpdateUrlRef and configure variables as described in PowerShell script parameters. Review the script and what it does and make sure to modify it to fit your needs and environment.
I do not take any responsibility or any kind of warranty for any damage or issue that might be caused or related to the script.
If you have old XLS files in your SharePoint portal configure the SP_UpdateUrlRef script to only download these to a folder using $downloadXLS=$true and other variables set to $false. Otherwise you may go to step 7.
Edit the Convert_XLS PowerShell script to use same XLS folder as you did in the previous script
Run Convert_XLS to convert the XLS files to XLSX (Note that you will need to have Office 2013 installed on the machine where you run this script)
If you converted old XLS files configure the SP_UpdateUrlRef script to only upload these to a folder using $uploadConvertedXLSFiles=$true and other variables set to $false. You may optionally set $deleteOldXLSFileAfterUpload = $false to delete the old XLS files.
Set $deleteOldXLSFileAfterUpload and $downloadXLS to $false. Set the “run for” variables that you wish to use to $true.
Run the script and review if any issues did occur

PowerShell script parameters

Parameter	Description
$runForText	Use configuration in $replaceItems to replace text within ODC, RSDS and PPSDC files. (Can easily be extended for other text files as well if you look through conditions in GetFilesFromSite function.
$runForExcelWebParts	Uses configuration in $replaceItems to replace text in Excel Web Access Web Parts Workbook Url property:
$runForExcelXLSX	Use configuration in $replaceItems to replace text in Excel workbook connection string. Uses configuration in $pureUrlReplaceItems to replace text within Excel workbook connection file property: *Connection file propery is usually an Url pointing to a ODC file within your SharePoint site
$runForSSRS	Use configuration in $ssrsDSMappings to match Report(s) data source names with Url of SSRS data connection file. When RDL report files are migrated to a new environment the old Url cannot be found and will become blank in new enivironment:
$xlsWorkDirRoot	Path to directory where XLS/XLSX files are downloaded/uploaded from
$downloadXLS	If true download all found XLS and XLM files to path defined in $xlsWorkDirRoot
$uploadConvertedXLSFiles	If true upload all XLSX/XLSM files from $xlsWorkDirRoot to original location (matching on file name and document library name without file extension)
$deleteOldXLSFileAfterUpload	If true old XLS files will be deleted when new XLSX files are uploaded
$SitesToIgnore	List of sites to ignore (based on site title)
$webAppUrls	List of web applications to iterate
$reportServerUri	Reporting Services Web Service (http://newportal.com/_vti_bin/ReportServer/ReportService2010.asmx?WSDL)
$ssrsReportDocLib	Root location for service $reportServerUri to look for reports (/ for root directory or http://newportal.com/site/doclib)
$username	User to access Reporting Services Web Service
$password	Password for above
$openXMLLibDir	Location of DocumentFormat.OpenXml.dll

Convert_XLS.ps1

PowerShell script to convert old XLS files to newer Microsoft Open Document format (XLSX).

Download PowerShell script (bundle)

Function SetEnglishCulture {
    $cultureName = "en-US"

	$currentThread = [System.Threading.Thread]::CurrentThread 
	$culture = [System.Globalization.CultureInfo]::CreateSpecificCulture($cultureName) 
	$currentThread.CurrentCulture = $culture 
	$currentThread.CurrentUICulture = $culture 
	Write-Host Culture chnaged to: $cultureName -ForegroundColor Green
}

# Run once again if this does not work...
SetEnglishCulture

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true
$folderpath = "C:\SP_UpdateUrlRef\FilesToConvert"
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse |
ForEach-Object `
{
	$path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
	
	"Converting $path"
	$workbook = $excel.workbooks.open($_.fullname)
	
	$path += ".xlsx"
	$workbook.saveas($path, $xlFixedFormat)
	$workbook.close()
	
	$oldFolder = $path.substring(0, $path.lastIndexOf("\")) + "\_original_xls"
	
	write-host $oldFolder
	if(-not (test-path $oldFolder))
	{
		new-item $oldFolder -type directory
	}
	
	move-item $_.fullname $oldFolder
	
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

SP_UpdateUrlRef.ps1

PowerShell script to update url and server references in SharePoint content. Useful after change of database or analysis services (olap) server or if SharePoint server have been migrated to new domain.