Array of Arrays: how to extract the sub-array values

PowerShell Help
1

I’m trying to automate the creation of a group of SQL Agent jobs, that each run a Group of SQL Agent jobs.

$group = $ReportingDBs
$i = 0
$GroupsList = @()
do {
    if( "$subgroup$i" ) { Remove-variable -Name "subgroup$i" }
    new-variable -Name "subgroup$i" -value $group[0..6]
    Write-Host "subgroup$i"
    $GroupsList += "subgroup$i"
    ++$i
    $group = $group[5..$group.length]
    
}
while ($group.length -gt 0)

$GroupsList

$ReportingDBs holds the list of database names, and the script creates a list of groups of those:$GroupsList

$GroupsList
subgroup0
subgroup1
subgroup2
subgroup3
subgroup4
subgroup5
subgroup6

In each of those groups, there’s a list of databases

$subgroup0
reporting_dev123
reporting_dev321
reporting_dev456
reporting_dev654
reporting_dev789
reporting_dev987
reporting_dev135

$subgroup1
reporting_dev246
reporting_dev357
reporting_dev468
reporting_dev579
reporting_dev680
reporting_dev136
reporting_dev791

I’m having difficulty with looping through each of the groups and executing a query against it’s members.

How can I work with an array of arrays ?

2

I don’t know if this is necessarily the most efficient way, but you should be able try something like

Foreach($group in $groupslist)
{
    Foreach($db in $group)
    {
        Do-Something
    }
}

There may be a more PowerShelly way to do this, but I think it would work.
Good luck
Liam

3

If I understand correctly you are looking at something like this.

$subgroup0 = @('reporting_dev123','reporting_dev321','reporting_dev456','reporting_dev654','reporting_dev789','reporting_dev987','reporting_dev135')
$subgroup1 = @('reporting_dev246','reporting_dev357','reporting_dev468','reporting_dev579','reporting_dev680','reporting_dev136','reporting_dev791')
$GroupsList = @($subgroup0,$subgroup1)

#For $subgroup0, which is in Position 0 of the $GroupList array
$GroupsList[0] | ForEach-Object {
    #Do Something
    Write-Host $_ -ForegroundColor Red
}

#For $subgroup1, which is in Position 1 of the $GroupList array
$GroupsList[1] | ForEach-Object {
    #Do Something different
    Write-Host $_ -ForegroundColor Green
}
4

Liam, Curtis, thank you for your help.
I had actually found a “solution” that’s working, I should have updated the post, sorry.
I also wanted to limit the number of jobs per group of jobs.
Anyway, the loop below did the trick for me.

$ReportingDBs = (Invoke-SqlCmd -ServerInstance $SqlServer -Database msdb -Query "SELECT  [name] FROM sys.databases WHERE [name] LIKE 'reporting_%'").name

$ArraySize = 6
$DBCount = ($ReportingDBs).Count
$TotalJobs = [math]::ceiling($DBCount/$ArraySize) 

$JobNames = @()
for ($i=1;$i -le $ArraySize; $i++) {
$JobName = "DW - SSIS_Daily Master DW ETL - Run All In batches - STEP 2 - $i"
$JobNames += $JobName
}

foreach($JobName in $jobnames){
# Check if job already exists. Then fail, rename or drop
$SQLJob = $SQLSvr.JobServer.Jobs[$JobName]
    if ($SQLJob)
    { 
      # Drop:
      Write-Host "Job with name $JobName found, removing it"
      $SQLJob.Drop()
    }
}

foreach($JobName in $jobnames){

#Create new (empty) job 
$SQLJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $($SQLSvr.JobServer), $JobName
$SQLJob.OwnerLoginName = "SA"
$SQLJob.Create() 
Write-Host "Job '$JobName' created"

$array = @()
$array = $ReportingDBs | Select -First $ArraySize $_

foreach($ReportingDB in $array) {
$array.Count
    $ReportingDBs.remove($ReportingDB) 
$SourceDB = $ReportingDB -replace 'reporting_', 'cs'  
$JobStepName = "$SourceDB - Exec Daily Master DW ETL - IsDailyLoad False"
.
.
.