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

This topic contains 3 replies, has 3 voices, and was last updated by  iain Barnetson 3 weeks, 6 days ago.

  • Author
    Posts
  • #82687

    iain Barnetson
    Participant

    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 ?

  • #82699

    Liam Kemp
    Participant

    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

  • #82702

    Curtis Smith
    Participant

    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
    }
  • #82807

    iain Barnetson
    Participant

    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"
    .
    .
    .
    

You must be logged in to reply to this topic.