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

1. Download powerShell script zip package (includes update script (SP_UpdateUrlRef) and XLS conversion script (Convert_XLS) and DocumentFormat.OpenXml.dll)
2. 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.
3. 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.
4. Edit the Convert_XLS PowerShell script to use same XLS folder as you did in the previous script
5. 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)
6. 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.
7. Set $deleteOldXLSFileAfterUpload and $downloadXLS to $false. Set the “run for” variables that you wish to use to $true.
8. 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.

Download PowerShell script (bundle)

# Script to Update url and server references in SharePoint content after SharePoint/sql server/sql analysis server migration
#
# Jonas Widriksson, 2015
#
# The script handles the following scenarios:
# - Search and replace text in files (ODC, RSDS (and whatever extension you like to include)
# - Search and replace text in Excel files embedded connection string
# - Search and replace text in Excel ODC Url connection string
# - Search and replace text Excel Viewer WebPart Url (reference to Excel file)
# - Map SSRS report data sources to mapping list below and assign the correct shared data source

# Configuration variables
# -----------------------

# Run for:
$runForText = $false
$runForExcelWebParts = $false
$runForExcelXLSX = $true
$runForSSRS = $false

#XLS Management
$xlsWorkDirRoot = "C:\SP_UpdateUrlRef\FilesToConvert"
$downloadXLS = $false
$uploadConvertedXLSFiles = $false
$deleteOldXLSFileAfterUpload = $false

#Sites to ignore by site title
$SitesToIgnore = "John Doe", "Special Site"

# List of site collections to run script on:
$webAppUrls = "http://mysite.newdomain.net" # "http://mysite.newdomain.net"; #"http://newportal.newdomain.net" #"http://newportal.newdomain.net" , "http://mysite.newdomain.net";

# Url of reporting services webservice and site collection to run ssrs mapping on:
$reportServerUri = "http://newportal.newdomain.net/_vti_bin/ReportServer/ReportService2010.asmx?WSDL"

# Url to ssrs site/doclib where to look in all children for ssrs reports
$ssrsReportDocLib = "http://newportal.newdomain.net/site/doclib"

# Used to authenticate with SSRS SharePoint webservice
$username = "SP_Farm"
$password = "********"

$openXMLLibDir = "C:\SP_UpdateUrlRef\Lib\DocumentFormat.OpenXml.dll"
$excelWebUIDLL = "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.Office.Excel.WebUI\v4.0_15.0.0.0__71e9bce111e9429c\Microsoft.Office.Excel.WebUI.dll"

# Name of file log:
$logFileName = "output-last-run.csv";

# All textfiles will be search and replaced with the values defined here:
# Note that it will replace items will searcha and replace of replaced values in list order (hence it can be a bit messed up if your connections use both server names and FQDN names)
# This is why sample list below have multiple "invalid" urls at the bottom to address this issue
$replaceItems = @(	
    # OLD_SQL_SERVER
    @{ 
		old="OLD_SQL_SERVER";
		new="OLD_SQL_SERVER.newdomain.net";       
	},    
	@{
		old="10.32.150.2"; 
		new="OLD_SQL_SERVER.newdomain.net";
	},        
    @{
        old="OLD_SQL_SERVER.old-domain.net";
        new="OLD_SQL_SERVER.newdomain.net";
    },

    #OLD_OLAP_SERVER
    @{ 
		old="OLD_OLAP_SERVER";
		new="OLD_OLAP_SERVER.newdomain.net";       
	},    
	@{
		old="10.32.150.3"; 
		new="OLD_OLAP_SERVER.newdomain.net";
	},        
    @{
        old="OLD_OLAP_SERVER.old-domain.net";
        new="OLD_OLAP_SERVER.newdomain.net";
    },

    # OLD_SQL_SERVER (Fix of text fixed above - might be needed)
    @{
        old="OLD_SQL_SERVER.newdomain.net.old-domain.net";
        new="OLD_SQL_SERVER.newdomain.net";
    },
    @{
        old="OLD_SQL_SERVER.newdomain.net.newdomain.net.old-domain.net";
        new="OLD_SQL_SERVER.newdomain.net";
    },
    @{       
        old="OLD_SQL_SERVER.newdomain.net.newdomain.net";
        new="OLD_SQL_SERVER.newdomain.net";
    },
    @{
        old="OLD_SQL_SERVER.newdomain.net.newdomain.net.newdomain.net";
        new="OLD_SQL_SERVER.newdomain.net";
    },

    # OLD_OLAP_SERVER (Fix of text fixed above - might be needed)
    @{
        old="OLD_OLAP_SERVER.newdomain.net.old-domain.net";
        new="OLD_OLAP_SERVER.newdomain.net";
    },
    @{
        old="OLD_OLAP_SERVER.newdomain.net.newdomain.net.old-domain.net";
        new="OLD_OLAP_SERVER.newdomain.net";
    },
    @{             
        old="OLD_OLAP_SERVER.newdomain.net.newdomain.net";
        new="OLD_OLAP_SERVER.newdomain.net";
    },
    @{
        old="OLD_OLAP_SERVER.newdomain.net.newdomain.net.newdomain.net";
        new="OLD_OLAP_SERVER.newdomain.net";
    },
    
    # Force MSOLAP provider version
    #@{
    #    old="MSOLAP.6";
    #    new="MSOLAP";
    #},

    # Special fix for RSDS files, somehow it seems that they get malformed with ??? in beginning of XML after first time script is run. Ensure to apply this to avoid this issue if it happens
    @{
        old="\?\?\?";
        new="";
    }
)

# All SSRS reports (found in webservice/sitecoll defined in reportServerUri) will be assigned rsds file from below:
$ssrsDSMappings = @(
    @{ dsName="OLD_SQL_SERVER"; dsNewUrl="http://newportal.newdomain.net/Data Sources For Reporting Services/OLD_SQL_SERVER.rsds"; },
    @{ dsName="OLD_SQL_SERVER_SupplyChain"; dsNewUrl="http://newportal.newdomain.net/Data Sources For Reporting Services/OLD_SQL_SERVER_SupplyChain.rsds"; },    
    @{ dsName="OLD_SQL_SERVER_Finance"; dsNewUrl="http://newportal.newdomain.net/Data Sources For Reporting Services/OLD_SQL_SERVER_Finance.rsds"; }
)

# Text replace mappings for Excel ODC Connection URL (Not this is not for actual connection string in Excel only ODC Url, 
# The actual connection string should be defined in replaceItems above
# This will also be used for replacement of urls in excel viewer webparts
$pureUrlReplaceItems = @(
    @{
        old="http://oldportal.old-domain.net";
        new="http://newportal.newdomain.net";
    },
    @{
        old="http://oldportal";        
        new="http://newportal.newdomain.net";
    },
    @{
        old="http://10.32.150.1";
        new="http://newportal.newdomain.net";
    },
    
    # XLS -> XLSX (To solve after XLS files have been converted to XLSX)
    @{
         old=".xls";
         new=".xlsx";
    }
)

# Other variables
# -----------------

$global:itemList = New-Object System.Collections.Generic.List[PSObject]

# Methods
# -------

Function CheckOutItem($item) 
{
    if ($item.List.ForceCheckout) { 
        Write-Host "Checking out: " $item.Title -ForegroundColor Gray
        $file.CheckOut() 
    } 
}

Function CheckInItem($item)
{
    if ($item.List.ForceCheckout) { 
        Write-Host "Checking in: " $item.Title -ForegroundColor Gray
        $file.CheckIn($true)

        if ($list.List.EnableVersioning) {
            Write-Host "Publishing: " $item.Title -ForegroundColor Gray
            $file.Publish("File checked/modified by migration script")
        }
    }
}

Function ReplaceTextInFile($item)
{      
    CheckOutItem $item

    $itemUpdated = $false

    $file = $item.File;
    
    $fileContent = [System.Text.Encoding]::ASCII.GetString($file.OpenBinary())
    $newFileContent = $fileContent

    $replaceItems | foreach-object  {      
        $newFileContent = $newFileContent -ireplace $_.old, $_.new
    }
        
    if ($fileContent -ne $newFileContent) {
        Write-Host "Updating: " $item.Url -ForegroundColor Green
        $file.SaveBinary([System.Text.Encoding]::ASCII.GetBytes($newFileContent))
        $itemUpdated = $true
    }
    
    CheckInItem $item

    return $itemUpdated
}

Function ReplaceUrlInExcelWebPArt($item)
{
    CheckOutItem $item

    $itemUpdated = $false

    $webPartManager = $web.GetLimitedWebPartManager($item.Url, [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared);
    $webParts = $webPartManager.WebParts;
    foreach($webPart in $webParts) 
    {
        if ($webPart.GetType() -eq [Microsoft.Office.Excel.WebUI.ExcelWebRenderer]) {

            $excelUri = $webPart.WorkbookUri
            $newExcelUri = $excelUri

            Write-Host "Found WorkbookUri: " $excelUri -ForegroundColor Yellow

            $pureUrlReplaceItems | foreach-object  { 
                $newExcelUri = $newExcelUri -ireplace $_.old, $_.new
            }

            if ($excelUri -ne $newExcelUri) {
                Write-Host "Updating: " $item.Url -ForegroundColor Green
                $webPart.WorkbookUri = $newExcelUri;
                $webPartManager.SaveChanges($webPart);
                $itemUpdated = $true
            }
            
        }
    }

    CheckInItem $item

    return $itemUpdated
}

Function ReplaceTextInExcelConnStrings($item)
{    
     $fileStream = $item.File.OpenBinarystream()
     $xlPackage = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($fileStream,$True)

     $doUpdate = $false

     # Check if document has any connections
     if ($xlPackage.WorkbookPart.ConnectionsPart) {


         $connStream = $xlPackage.WorkbookPart.ConnectionsPart.GetStream()
         $xmlDoc = New-Object System.Xml.XmlDocument
         $xmlDoc.Load($connStream) #= [System.Xml.XmlDocument](Get-Content $connStream)
         #$xmlDoc

         $nsManager = [System.Xml.XmlNamespaceManager]$xmlDoc.NameTable;
         $nsManager.AddNamespace("default", $xmlDoc.DocumentElement.NamespaceURI);
         $nodelist = [System.Xml.XmlNodeList]$xmlDoc.SelectNodes("//default:connections/default:connection", $nsManager);                 
         
         foreach ($node in $nodelist) {
            # Fix ODC connection
            if ($node.Attributes["odcFile"]) {               

                $odcFileUrl = $node.Attributes["odcFile"].Value
                $newOdcFileUrl = $odcFileUrl 


                $pureUrlReplaceItems | foreach-object  { 
                    $newOdcFileUrl = $newOdcFileUrl -ireplace $_.old, $_.new
                }

                if ($odcFileUrl -ne $newOdcFileUrl) {
                    Write-Host "Updating: " $item.Url " odc connection string" -ForegroundColor DarkGreen                        
                    $node.Attributes["odcFile"].Value = $newOdcFileUrl
                    $doUpdate = $true
                }                
                
            }
            else {
                Write-Host "Connection: " $node.name " ODC Url will not be modified" -ForegroundColor Gray
            }

            # Fix embedded connection string
            if ($node.dbPr) {
                if ($node.dbPr.attributes["connection"]) {

                    $connStr = $node.dbPr.attributes["connection"].Value
                    $newConnStr = $connStr

                    $replaceItems | foreach-object  { 
                        $newConnStr = $newConnStr -replace $_.old, $_.new
                    }

                    if ($connStr -ne $newConnStr) {
                        Write-Host "Updating: " $item.Url " embedded connection string" -ForegroundColor DarkGreen                        
                        $node.dbPr.attributes["connection"].Value = $newConnStr
                        $doUpdate = $true
                    }
                }
            }          
            else {
                Write-Host "Connection: " $node.name " connection string will not be modified" -ForegroundColor Gray
            }            
         }

         if ($doUpdate) {
            Write-Host "Updating excel file: " $item.Url -ForegroundColor Green

             $streamWriter = new-object System.IO.StreamWriter($xlPackage.WorkbookPart.ConnectionsPart.GetStream([Io.FileMode]::Create))
             $xmlDoc.Save($streamWriter.BaseStream)

             $item.File.SaveBinary([System.IO.Stream]$fileStream)

             $streamWriter.Dispose()
             $xlPackage.Dispose()
         }
     }
     else {
        Write-Host "No connections found in: " $item.Url -ForegroundColor Gray
     }

     return $doUpdate
}

Function LogItem($currentSiteUrl, $currentWebStr, $listStr, $itemUrlStr, $commentStr)
{
    $listItem = New-Object PSObject -Property @{
        webAppUrl = $currentSiteUrl;
        SiteUrl = $currentWebStr; 
        DocumentLibrary = $listStr;        
        Url = $itemUrlStr;
        Comment = $commentStr
        }
    $global:itemList.Add($listItem)
}


Function GetFilesFromSite([Microsoft.SharePoint.SPWeb] $currentWeb) {

    # Check if current web is in ignore list
    $isIgnored = $false

    foreach ($ignoreSite in $SitesToIgnore) {
        if ($currentWeb.Title -eq $ignoreSite) {
            $isIgnored=$true
        }
    }

    if (-not $isIgnored) {    

    $itemsToDelete = @()        

    foreach($list in $currentWeb.lists)    {      
      if ( ($list.BaseType -eq "DocumentLibrary") -and ( ($list.DefaultViewUrl -notlike "*_catalogs*") -and ($list.DefaultViewUrl -notlike "*Style Library*")))
      {     
        try {   
            $itemCount = $list.ItemCount
            $itemCollection = $list.Items

            for ($i=0; $i -le $itemCount; $i++) {
                try {

                $item = $itemCollection[$i]

                    # ODC, RSDS, PPSDC Files
                    if ($runForText) {
                        if ($item.Url -like "*.ppsdc" -or $item.Url -like "*.rsds" -or $item.Url -like "*.odc") {                        
                            Write-Host "Processing file: " $item.Url -ForegroundColor Gray                 
                            $updated = ReplaceTextInFile $item $global:oldText $global:newText

                            if ($updated) {
                                LogItem $currentWeb.Site.Url $currentWeb $list $item "File modified"
                            }
                        }
                    }

                    # Excel Viewer Web Parts
                    if ($runForExcelWebParts) {
                        if ($item.Url -like "*.aspx") {
                            Write-Host "Processing page: " $item.Url -ForegroundColor Gray
                            $updated = ReplaceUrlInExcelWebPArt $item
                    
                            if ($updated) {
                                LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url "Page ExcelWebRenderer WebPart modified"
                            }
                        }
                    }

                    # Excel Data Connections (embedded in Excel files)
                    if ($runForExcelXLSX) {
                        if ($item.Url -like "*.xlsx" -or $item.Url -like "*.xlsm")  { #-or $item.Url -like "*.xls") {
                            Write-Host "Processing Excel file: " $item.Url -ForegroundColor Gray
                            $updated = ReplaceTextInExcelConnStrings $item

                            if ($updated) {
                                LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url "Excel connection modified"
                            }
                        }
                    }

                    # Download XLS Files   
                    if ($downloadXLS) {
                        if ($item.Url -ilike "*.xls" -or $item.Url -like "*.xlm") {
                            Write-Host "Downloading old format Excel workbook: " $item.Url -ForegroundColor Gray

                            $isDuplicate = $false
							
							$folderName = "";

							if ($item.File.ParentFolder) {
								$folderName = "/" + $list.Title + "/" + $item.File.ParentFolder
								Write-Host "ParentFolder: " + $folderName -ForegroundColor Yellow
							}
							else {
								$folderName = "/" + $list.Title
							}
							
                            $destDir = $xlsWorkDirRoot +"/" + $currentWeb.Title  + $folderName

                            if (!(Test-Path -path $destDir))
                            {
                                $destCreate = New-Item $destDir -type directory 
                            }

                            Get-ChildItem -Path $destDir -Include $filetype -recurse | ForEach-Object {
                                if ($_.Name -eq $item.File.Name) {
                                    Write-Host "File already exists!" -ForegroundColor Red
                                    LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url "Duplicate XLS Name!"  
                                    $isDuplicate = $true                          
                                }
                            }

                            if (-not $isDuplicate) {
                                #Download file
                                $binary = $item.File.OpenBinary()
                                $stream = New-Object System.IO.FileStream($destDir + "/" + $item.File.Name), Create
                                $writer = New-Object System.IO.BinaryWriter($stream)
                                $writer.write($binary)
                                $writer.Close()

                                LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url "Downloaded XLS"       
                            }                     
                        }
                    }

                    # Upload converted XLS (to XLSX) files
                    # This assumes we have *.xlsx files in $xlsWorkDir with same name as XLS files downloaded with $downloadXLS=true
                    # The conversion has to be done manually (or with other script) hence we cannot run both $downloadXLS=true and 
                    # $uploadConvertedXLSFiles at the same time
                    # Download XLS Files   
                    if ($uploadConvertedXLSFiles) {

                    
                        if ($item.Url -like "*.xlsx" -or $item.Url -like "*.xlm") {
                                         
                            Write-Host "Found fixed XLSX workbook: " $item.Url -ForegroundColor Gray
							
							$folderName = "";

							if ($item.File.ParentFolder) {
								$folderName = "/" + $list.Title + "/" + $item.File.ParentFolder								
							}
							else {
								$folderName = "/" + $list.Title
							}
							
                            $xlsWorkDir = $xlsWorkDirRoot +"/" + $currentWeb.Title  + $folderName + "/"

                            if (Test-Path $xlsWorkDir) {
                                Write-Host "Looking for new format XLSX workbook on disk at: " $xlsWorkDir -ForegroundColor Gray

                                Get-ChildItem -Path $xlsWorkDir | ForEach-Object {
                                    if ($_.Name -eq $item.File.Name) {
                                                                    
                                        Write-Host "Found converted XLSX file! Try to upload..." -ForegroundColor Green  
                                    
                                        if ($item.File.ParentFolder) {
                                            $uploadFolder = $item.File.ParentFolder
                                        }
                                        else {
                                            $uploadFolder = $list
                                        }
                                                                        
                                        # Add code to upload da shit!
                                        $doesFileExist = $false
                                        foreach ($f in $uploadFolder.Files) {
                                            if($f.Name -eq $_.Name) {
                                                $doesFileExist = $true
                                            }
                                        }

                                        if ($doesFileExist) {                                        
                                            Write-Host "Note: File already exists will overwrite..." -ForegroundColor Red
                                        }
                                    
                                        #Get the file stream of each file, assign it to a variable.
                                        $Stream = (Get-Item $_.fullname).openread()                                            
                                        $NewFile = $uploadFolder.Files.Add($_.Name, $Stream, $True)
                                            
                                        CheckInItem($NewFile)

                                        #Commit these changes
                                        $NewFile.Update()
                                        Write-Host "Done!" -ForegroundColor Green

                                        LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url "Uploaded Converted XLSX file"
                                                                                        
                                        if ($deleteOldXLSFileAfterUpload) {
                                            Write-Host "Queue item for delete: " $item.Url -ForegroundColor Gray
                                            $itemsToDelete += $item
                                        }

                                    }
                                }
                            }
                            else {
                                Write-Host "Directory: " $xlsWorkDir " does not exist..." -ForegroundColor White
                            }
                            
                        }
                    }
                    
                }
                catch [Exception] {
                    Write-Host "Error occured and was logged for item: " $item.Url -ForegroundColor Red
                    $comment = "X Error: " + $_.Exception.Message
                    Write-Host $comment
                    LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $item.Url $comment
                }
            
            }
          }
          catch [Exception] {            
            Write-Host "Error occured and was logged for list: " $list.Url -ForegroundColor Red
            $comment = "X List Error: " + $_.Exception.Message
            Write-Host $comment
            LogItem $currentWeb.Site.Url $currentWeb.Url $list.Title $list.Description $comment
          } 
        }
    }

    if ($deleteOldXLSFileAfterUpload) {
        foreach ($item in $itemsToDelete) {
            Write-Host "Deleting old XLS file: " $item.Url -ForegroundColor Yellow
            LogItem $currentWeb.Site.Url $currentWeb.Url $item.File.ParentFolder $item.Url "Deleting old XLS file..."

             $r = $item.Recycle()
                                        
            Write-Host "Done!" -ForegroundColor Yellow
        }
    }
}
else {
    Write-Host "This site is ignored: " $currentWeb.Title -ForegroundColor Yellow
    }
}

Function UpdateReportDataSources()
{
    Write-Host "Updating SSRS reports data sources (using SSRS webservice)"  -ForegroundColor White
    Write-Host "Using SSRS web service: " $reportServerUri -ForegroundColor White

    $secstr = New-Object -TypeName System.Security.SecureString
    $password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
    $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr
    $Proxy = New-WebServiceProxy -Uri $reportServerUri -Credential $cred
    
    # Gather the webservice types for later use 
    $WebServiceTypes = @{}
    foreach ($Type in $Proxy.GetType().Assembly.GetExportedTypes())
    {
        $WebServiceTypes.Add($Type.Name, $Type.FullName);
    }

    $Proxy.Timeout = 600000

    $ReportItems = $Proxy.ListChildren($ssrsReportDocLib, $true) | Where-Object { $_.Name -Like "*.rdl" }

    foreach ($ReportItem in $ReportItems)
    {
        Write-Host "Processing report: " $ReportItem.Path -ForegroundColor Gray

        $reportDataSources = $Proxy.GetItemDataSources($ReportItem.Path)

        $doDSUpdate = $TRUE;  
        
        try {  
                
            for ($i=0; $i -lt $reportDataSources.length; $i++) {
                $foundDS = $FALSE;

                foreach ($dsMapping in $ssrsDSMappings) {
                    if ($reportDataSources[$i].Name -eq $dsMapping.dsName) {                        
                        $foundDS = $TRUE;

                        # Declare DataSourceReference object
                        $ref = New-Object $WebServiceTypes.DataSourceReference
                        $ref.Reference = $dsMapping.dsNewUrl;

                        # Declare DataSourceReference object
                        $ds = New-Object $WebServiceTypes.DataSource
                        $ds.Name = $dsMapping.dsName
                        $ds.Item = $ref

                        $reportDataSources[$i] = $ds
                    }                
                }

                if (!$foundDS) {                
                    Write-Host "Miss mapping for: " $reportDataSources[$i].Name -ForegroundColor Red
                
                    $comment = "Miss mapping for: " + $reportDataSources[$i].Name
                    LogItem "Problem SSRS Update" "N/A" "N/A" $ReportItem.Path $comment

                    $doDSUpdate = $FALSE                
                }
            }

            if ($doDSUpdate) {
                Write-Host "Updating data source(s) on: " $ReportItem.Path -ForegroundColor Green

                LogItem "SSRS Update" "N/A" "N/A" $ReportItem.Path "Updated data sources"

                $Proxy.SetItemDataSources($ReportItem.Path, $reportDataSources)
            }
            else {
                Write-Host "Skipped update of data source(s) on: " $ReportItem.Path -ForegroundColor Red            
            }
        
        }
        catch [Exception] {
            Write-Host "Error occured and was logged for report/datasource: " $ReportItem.Path -ForegroundColor Red
            $comment = "X SSRS DS Error: " + $_.Exception.Message
            Write-Host $comment
            LogItem "SSRS Update" "N/A" "N/A" $ReportItem.Path $comment
        }
    }


    Write-Host "Logging not used DS mappings"  -ForegroundColor White
    $ssrsDSMappings | ForEach-Object { 
        LogItem "SSRS DS Mapping not used" "N/A" "N/A" $_.dsName $_.dsNewUrl
    }

    $Proxy.Dispose()
}



Function LoadDependencies()
{
    Write-Host "Loading dependencies" -ForegroundColor White

    if ((Get-PSSnapin -Name Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue) -eq $null) {
        Add-PSSnapin Microsoft.SharePoint.Powershell
    }

    $openXMLlib = [System.Reflection.Assembly]::LoadFrom($openXMLLibDir)
    $excelWebUI = [System.Reflection.Assembly]::LoadFrom($excelWebUIDLL)

    #[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Office.Excel.WebUI.ExcelWebRenderer')
}

# Script start
# -----------------------------


#[Microsoft.SharePoint.SPSecurity]::RunWithElevatedPrivileges({

    Write-Host "Script started at" (Get-Date).ToString()

    LoadDependencies

    if ($runForText -or $runForExcelWebParts -or $runForExcelXLSX -or $downloadXLS -or $uploadConvertedXLSFiles -or $deleteOldXLSFileAfterUpload) {
        foreach($webAppUrl in $webAppUrls)
        {                        
            $SPWebApp = Get-SPWebApplication $webAppUrl

            Write-Host "Web Application: " $SPWebApp.Url -ForegroundColor Cyan

            foreach ($siteCollection in $SPWebApp.Sites ) {
                 
                # If you would like to limit on certain site/mysite                      
                #if ($siteCollection.Url -ilike "*personal/U123456") {

                    Write-Host "Site Collection: " $siteCollection.Url -ForegroundColor Cyan
        
                    foreach($web in $siteCollection.AllWebs) {
                        Write-Host "Web: " $web.Url -ForegroundColor White
                        LogItem "MySite:" "N/A" "N/A" $web.Title $web.Url
                        GetFilesFromSite $web    
                        $web.Dispose()
                    }        
                #}

                $siteCollection.Dispose()
            }            
        }
    }
    else {
        Write-Host "No variable configured to run action against. Check if statement in script." -ForegroundColor Cyan
    }

    if ($runForSSRS) {
        UpdateReportDataSources
    }

    
    Write-Host "Writing log file: " $logFileName
    $itemList | Select-Object webAppUrl, SiteUrl, DocumentLibrary, Url, Comment | Export-Csv $logFileName -NoType

    # release the array since it will be quite large
    $itemList = $null

    Write-Host "Script finished at" (Get-Date).ToString()
#});