Change Connection datasource path for Tabular databse

Question

I am trying to change Connection Datasource Path for MSSQL 2017. My code:

$ServerName="localhost\olap17"
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
foreach ($dbase in $server.Databases )
{
Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB; Data Sources: {3} " -f $dbase.Name, $dbase.State, ($dbase.EstimatedSize/1024/1024).ToString("#,##0"), $dbase.Model.DataSources.Count )
foreach ($dsource in $dbase.Model.DataSources)
{
    $dsource.ConnectionDetails.Address.Path = $dsource.ConnectionDetails.Address.Path.Replace('OLDSERVER', 'NEWSERVER')
    echo ">>> Datasource: " $dsource.ConnectionDetails.ToString()
}
}

Output appears correct, but when I check out Connection properties, I see path unchanged:

Database: Billing Cycle Delivery; Status: Unprocessed; Size: 0MB; Data Sources: 5 
Datasource: 
{
"protocol": "file",
"address": {
"path": "\\\\NEWSERVER\\Telco\\Models\\Billing Cycle Delivery\\Data Sources\\Billing.xlsx"
},
"authentication": null,
"query": null
}

Looking like new properties not passed back to server... Where is my problem? I can't find any useful information on the Internet :(

Any ideas appreciated!

Answer 1

I found a solution. Needed:

$dbase.Update(1)

This is required to push changes to the server.

My final script, if somebody needs:

$changeSource = 'OLDSERVER'
$changeTarget = 'NEWSERVER'
$ServerName="localhost\olap17"
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}
foreach ($dbase in $server.Databases )
{
 Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB; Data Sources: {3} " -f $dbase.Name, $dbase.State, ($dbase.EstimatedSize/1024/1024).ToString("#,##0"), $dbase.Model.DataSources.Count )
 foreach ($dsource in $dbase.Model.DataSources)
 {
    $dsource.ConnectionDetails.Address.Path = $dsource.ConnectionDetails.Address.Path.Replace($changeSource, $changeTarget)
    echo ">>> Datasource name: " $dsource.Name " Connection: " $dsource.ConnectionDetails.ToString()
    $dbase.Update(1)
}
}
$server.Disconnect($ServerName)
echo "Connection closed"
echo "------------- Confirmation --------------"
$server.connect($ServerName)
foreach ($dbase in $server.Databases )
{
 Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB; Data Sources: {3} " -f $dbase.Name, $dbase.State, ($dbase.EstimatedSize/1024/1024).ToString("#,##0"), $dbase.Model.DataSources.Count )
 foreach ($newds in $dbase.Model.DataSources)
 {
    echo ">>> Datasource name: " $newds.Name " Connection: " $newds.ConnectionDetails.ToString()
 }
}
$server.Disconnect($ServerName)

Here is MS article that help me: https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.database?redirectedfrom=MSDN&view=sqlserver-2016