Change Data source in ODC files

This script updates the data source in ODC (Office Data Connection) files. This kind of file is a connection information file that contains properties to connect to, and retrieve data from an external data source or a Reporting Services Report. It includes a connection string, data queries, authentication information, and other settings. They can be created using the Data Connection Wizard in Microsoft Excel that generates the connection information using HTML markup with included segments of XML. ODC files are stored using HTML and XML. You will find here information about ODC file usage in Reports.

These are the steps performed by the script:

the user odc files are located in their home folders (\\nas_server\homes$\[username]\My Documents\My Data Sources)
change the old datasources (db_old1 and db_old2) to the new datasource (db_datasource)
get all the odc file to update (odc files with old datasources)
backup the current odc file before updating it
check if the backup is successful or not
if the backup file is ok, update the odc file with the new datasource

Script :

$NAS = "nas_server"
$user_folders = get-childitem \\$NAS\homes$
foreach ($user_home in $user_folders) {
$username = $user_home.name
if (Test-Path "\\$NAS\homes$\$username\My Documents\My Data Sources") {
$filelist_1 = Get-ChildItem "\\$NAS\homes$\$username\My Documents\My Data Sources\*.*" -include *.odc | Select-String -pattern "Data Source=db_old1"
$filelist_2 = Get-ChildItem "\\$NAS\homes$\$username\My Documents\My Data Sources\*.*" -include *.odc | Select-String -pattern "Data Source=db_old2"
       if (($filelist_1) -or ($filelist_2)) {
Write-Host "-------------------------------------------------------------------------------"
Write-Host "ODC file(s) to update for the user : $username"
Write-Host "-------------------------------------------------------------------------------"
}
else {
Write-Host "ODC file OK for $username"
}
       foreach ($file1 in $filelist_1){
if ($file1){
$source1 = ($file1).tostring().split(":")[0]
$dest1 = $source1+"bkp"
Copy-Item -Path $source1 -Destination $dest1
if (test-path $dest1){
                    Write-Host "$dest1 backup created successfully" -ForegroundColor Green
                    $new1 = (Get-Content $source1) -replace "Data Source=db_old1", "Data Source=db_datasource"
Set-Content $source1 $new1
                 }
                 else {Write-Host "$dest1 backup problem" -ForegroundColor red}
}
}    foreach ($file2 in $filelist_2){
if ($file2){
$source2 = ($file2).tostring().split(":")[0]
$dest2 = $source2+"bkp"
Copy-Item -Path $source2 -Destination $dest2
if (test-path $dest2){
                    Write-Host "$dest2 backup created successfully" -ForegroundColor Green
                    $new2 = (Get-Content $source2) -replace "Data Source=db_old2", "Data Source=db_datasource"
Set-Content $source2 $new2
}
else {Write-Host "$dest2 backup problem" -ForegroundColor red}
}
}
}
}

My Powershell script categories

Change Data source in ODC files

Leave a Reply Cancel reply