How to alter connection string using powershell?

PowerShell Help
1

Apparently the databases/cubes in an analysis server contain “script connection as > alter” option

I want to use a powershell script like this:

    $hashtable = @{}
Import-Csv "CSV_file" | ForEach-Object {
    $hashtable += @{$($_.Server) = ($_.Cube -split '\s*,\s*') }
}

Import-Module SqlServer

foreach($server in $hashtable.Keys){ 

   $Analysis_Server = New-Object Microsoft.AnalysisServices.Server  
   $Analysis_Server.connect("$server") 

   foreach($CUBE in $hashtable[$server]) {

      ####### Setting connection property for $Cube #######
"  
    <Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>$CUBE</DatabaseID>
    </Object>
    <ObjectDefinition>
        <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xsi:type="RelationalDataSource">
            <ConnectionString>Connection Timeout=60;User Id=someID;Password=pass;Data Source=td.domain.com;Persist Security Info=True;Session Character Set=UTF8</ConnectionString>
        </DataSource>
    </ObjectDefinition>
    </Alter>
    "

    }
}

that takes input a csv file

Server,Cube

server1.domain.com,Database1


and loops through the databases/cubes in the server to alter their DataSource ``

However i am sure that there is something missing in that script, but the thing is if i run that XML in SSMS, it complain about the Name element for DataSource is required. i understand that the full XML is needed, but it wont work from a script perspective that i am trying to accomplish because i cant have the same DataSource for other databases i have in the server. they are all possibly different, so the ID, Name, etc…have to be removed from the XML query in powershell script.

i receive this error

The object definition supplied for the ALTER statement is of a different type that the object reference to be altered.
2

Unfortunately, this isn’t really a PowerShell question per se. The error you’re getting is from the database server. Also, note that you can’t just paste XML here in the forums. You’d need to make a Gist of it in order to get raw XML to display.

I’m not sure I’d personally be able to help, here, because I don’t understand entirely what you’re asking the database server to do. It might be worth asking in a more SQL Server-specific forum, or on StackOverflow.com, which is a bit broader…?

3

[quote quote=124194]Unfortunately, this isn’t really a PowerShell question per se. The error you’re getting is from the database server. Also, note that you can’t just paste XML here in the forums. You’d need to make a Gist of it in order to get raw XML to display.

I’m not sure I’d personally be able to help, here, because I don’t understand entirely what you’re asking the database server to do. It might be worth asking in a more SQL Server-specific forum, or on StackOverflow.com, which is a bit broader…?

[/quote]
It’s ok, I have figured it out :slight_smile:

Import-Module SqlServer

$newConnectionString = "Connection Timeout=60;User Id=SOME_NEW_ID;Data Source=10.10.19.10;Persist Security Info=True;Session Character Set=UTF8"

$svr = new-Object Microsoft.AnalysisServices.Tabular.Server
$svr.Connect("server1.domain.com")

$svr.databases[1].model.datasources[0].ConnectionString = $newConnectionString
$svr.Databases[1].Update([Microsoft.AnalysisServices.UpdateOptions]::ExpandFull)