PowerShell Parallel API Data Retrieval

Question

I'm trying to create a PowerShell script for retrieving JSON data from a Rest API. This script would run as a SQL Server Agent Job, and insert the final data table with everything collected into the database.

I've explored -Parallel option for the Foreach loop, added it to a workflow, but get blocked when trying to write data to the datatable because it seems to be treated differently than just a simple object.

Is this something that could be done with jobs? Is there another way I could run this in parallel? Does the whole thing need restructured? Ideally this would run in just native PowerShell code without having to install any other modules.

The basic structure of my code today is:

#Compose URL

#Create a New-Object system.Data.DataTable and configure it

#Make Invoke-RestMethod API call to get the collection of objects

Foreach($object in $Collection){
#Make API call for details about each object
#Add data to datatable
}

#Write datatable to database

Matt, the current job takes about 12 hours to run. If I change it from adding data to a table object to just count stats in each object, where I can use the for each -parallel, it takes about 20 minutes to run. The first API I need to hit, once a day is fine, but I've got other sources I'll want to use the same pattern for that may take even longer. — ADataGMan

AdamL AdamL · Accepted Answer · 2017-08-14T09:20:24

Why don't you use jobs for this?

# define script block with api call
$JobSb = {
  param($obid)

  # make api call

  write-output = [PsCustomObject]@{
    Obid = $obid
    ApiResult = 0;
  }
}

# start jobs for all objects
Foreach ($object in $Collection) {
    Start-Job -ScriptBlock $JobSB -ArgumentList $object.Id -Name $object.name
}

# wait for jobs to finish
while (get-job) {
    Get-Job | Where-Object State -NE 'Running' | ForEach-Object {
        if ($_.State -ne 'Completed') {
            Write-Warning ('Job [{0}] [{1}] ended with state [{2}].' -f $_.id,$_.Name,$_.State)
        } else {
            $JobResults = @(Receive-Job $_)
        }

        # write results to datatable

        remove-job $_
    }
    start-sleep -Seconds 1
}

Api calls will be made in parallel. You may want to control the number of jobs running at the same time if there's a lot of them.

PowerShell Parallel API Data Retrieval

1 Answers