How can I loop through Query result rows?

[catastrophe2]

I have a table that contains some cubes with servers and active status (see attached image):

note: CUBE1 is repeated intentionally.

The idea is the same cube is getting synced from the same server 1 as a data source (process_server) onto different target servers (query_server; say server2 is in region A and server 3 is in region B).

The way the environment is set up is both servers have to be active/active status (meaning TRUE) except for some additional test cases, i am setting to false sometimes one or the other.

I have the following script now calling the cube and creating an XML file and replacing the placeholder in the XML file with the cube name as well as data source (query_server) name.

    Function Query($Query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$Database;Integrated Security=SSPI" 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.Connection = $SqlConnection 
    $SqlCmd.CommandText = $Query 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $a=$SqlAdapter.Fill($DataSet)
    $SqlConnection.Close() 
    $DataSet.Tables[0] }

    $CUBE = Query "SELECT DISTINCT[cube_name] FROM [$cubeTable] WHERE [cube_name] = '$CUBE_input' AND [active] = 'TRUE'" | Select -ExpandProperty cube_name;
write-host "`r`n - CUBE:   $CUBE" 

if (-Not ($CUBE)) {
    write-host "`r`n** $CUBE_input does NOT exist in the table! :( **" -foregroundcolor red -backgroundcolor black
}
else { 
write-host "`r`n**** $CUBE_input exists in the table! :) ****" -foregroundcolor yellow -backgroundcolor black
}

#DISTINCT ensures first unique result is passed only, so it only selects ONE row

$Data_Source = Query "SELECT DISTINCT[process_server] FROM [$cubeTable] WHERE [cube_name] = '$CUBE' AND [active] = 'TRUE'" | Select -ExpandProperty process_server;
write-host "`r`n + Data Source:   $Data_Source"   

#Destination server is query server
$Destination_Server = Query "SELECT DISTINCT[query_server] FROM [$cubeTable] WHERE [cube_name] = '$CUBE' AND [active] = 'TRUE'" | Select -ExpandProperty query_server;
write-host " > Target Server: $Destination_Server" 

$BasePath = Query "SELECT [variable_value] FROM [$pathTable] WHERE [variable_name] = 'base_path'" | Select -ExpandProperty variable_value; 

$xmlPATH = Join-Path -Path $BasePath -ChildPath $xmlDirectory
# creating a directory JSON_files. since this tries creating new directory even if it exists, -Force tells it to ignore the warning and not create the existing directory
New-Item -ItemType Directory -Force -Path $xmlPATH | out-null 

# New-Item returns the created FileInfo or DirectoryInfo object.
# out-null suppresses such output from displaying

$XML_file = Join-Path $xmlPATH $CUBE_input | %{ ($_ + ".xml") } # $_ is a placeholder to add file extension

$syncPATH = Join-Path -Path $BasePath -ChildPath $sync_output_Directory
New-Item -ItemType Directory -Force -Path $syncPATH | out-null

$sync_output = Join-Path $syncPATH $CUBE_input | %{ ($_ + ".txt") }

Then, the XML is passed to an Invoke_ASCMD command that syncs upon that cube and data source information in the XML file onto the targeted query servers.

    $xml = @'
<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Source>
    <ConnectionString>Provider=MSOLAP.8;Data Source=DataSource;Integrated Security=SSPI;Initial Catalog=CUBE</ConnectionString>
    <Object>
      <DatabaseID>CUBE</DatabaseID>
    </Object>
  </Source>
  <SynchronizeSecurity>CopyAll</SynchronizeSecurity>
  <ApplyCompression>true</ApplyCompression>
</Synchronize>
'@

#Trying to replace the DatatSource and CUBE variables using chain replace...

($xml).Replace("DataSource", $Data_Source).Replace("CUBE", $CUBE) | Out-File $XML_file

#Invoking Analysis Services SQL cmdlet...

    Invoke-ASCmd –InputFile $XML_file -Server $Destination_Server >$sync_output

This script right now works if the CUBE1 exists ONCE in the table and outputs one XML file, and an output file with the cube name. but of course, since i have two environments and two different query_servers to sync on, i needed to reduplicate the cube in the table like that.

with that being said, how can i loop the $CUBE so that the script syncs on the CUBE1 on the two servers?

so what i want to happen is the loop that will be integrated will run on the first row of CUBE1, get the destination server (i.e. query server2), execute the command, output, then loop again on the second row of CUBE1, get query server3 and execute once again, ourputting another xml file that would have the server2 appeneded to it so that the invoke command recognizes it as a different file to sync once again upon the second query server.

essentially, there would be TWO xml files, each named as such "CUBE1_queryserver2.xml" and another file "CUBE1_queryserver3.xml"

i have researched about looping through query result, and i found it can be done like

foreach($Row in $CUBE) {#do something}
but in my case, its a query function, so it doesnt really return rows as result but rather the whole table contained in dataset in Query function.

I also looked into dataset.Rows but again the $Result is the query function not a dataset.

[aldeene]

one question first.. how do you read the value from the dataset being returned?

[pinel]

This should do it for you

foreach ($Row in $CUBE.Tables[0].Rows) {
    # Do Something
}