PowerShell script parallelization effort - A job does not wait until it gets an answer from the REST API and finishes the job prematurely

Question

Edited with last version code:

I have a PowerShell script which is doing following steps:

1. Connect to SQL server, download table of 3 variables (three fields)
2. Then Connect to REST API (based on three variables) and GET JSON answer
3. Then save JSON output from API as file one by one, till script reach end of input array pool

This script runs just fine when steps are done in serial setting. Original script with while loop taking input items one be one, connect to API, get answer, save JSON answer to a file.

The problem: However it is enormously slow this way. Each cycle take around 1 minute and i have around 11,000 entries to process. It would take almost 7 days to finish.

The solution: based on this article: text. I have decide to use multi-thread job solution, where I can run multiple jobs in parallel.

The Issue: In this multi-tread solution, it seems, like each standalone JOB is finished prematurely. In few milliseconds whole batch of 11K files are saved, with proper notion of each variable. But all files are empty. I would still expect (even for parallel run), that each JOB last 1 min in average. I am convinced, that script simply does not wait till it gets answer from API and immediately jump to next step save an empty file.

Here is the PowerShell script:

#Connect to SQL server
$SQLServer = "XXXXXXXXXXXXX" #use Server\Instance for named SQL instances
$SQLDBName = "XXX"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security=True;"
$SqlConnection.Open()

$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $SqlConnection

$DataSet = Invoke-Sqlcmd -Query "SELECT Input_1 ,Input_2 ,Input_3  FROM XXX.dbo.API_Input"


#Close SQL Connection
$SqlConnection.Close()

$Commnad_Block = {
    Param( [string]$Variable_Input_1, [string]$Variable_Input_2, [string]$Variable_Input_3)
           
      # Compile URL link based on input
    $URL = 'https://example.com/Stag?Seg='+ $Variable_Input_2 +'&Cust='+$Variable_Input_1+'&Prod='+$Variable_Input_3

    # Call REST API GET method, with 3 variables
    $response = Invoke-RestMethod -Uri $URL -Method GET -ContentType "application/json" 

    #Create File name to be exported
    $FileNamePath = 'D:\File_Export\'+$Variable_Input_3+'.json'

    # Store JSON output from API into JSON raw file, 
    $response | ConvertTo-Json -depth 100 | Out-File $FileNamePath
}


#Remove all jobs
Get-Job | Remove-Job
$MaxThreads = 4

#Start the jobs. Max 4 jobs running simultaneously.
foreach($element in $DataSet){
    While ($(Get-Job -state running).count -ge $MaxThreads){
        Start-Sleep -Milliseconds 1
    }
    Start-Job -ScriptblVariable_Input_3k $Commnad_Block -ArgumentList $element.Input_1, $element.Input_2, $element.Input_3
}

#Wait for all jobs to finish.
While ($(Get-Job -State Running).count -gt 0){
    start-sleep 1
}

#Get information from each job.
foreach($job in Get-Job){
    $info= Receive-Job -Id ($job.Id)
}

#Remove all jobs created.
Get-Job | Remove-Job

Attempt to solve the issue: I have tried to use various wait statements, to give each JOB to get answer from API, but nothing seems to help. Still even that would not be desired solution. I would like enforce script to wait till API part is finished. I am clueless. I will emphasize that script it self is running just fine, if it is serialized.

Answer 1

I have been able to solve the issue. By actually pulling error massage into the file. After i got error found on web that problem is not the premature ending of a Job as i thought, but error was due to TLS version used on the server. I just put this line into the right place in the code and now it is working as expected:

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

As I mention code was working just fine if it was execute in serialized way. And it is because i had Tls1.2 set in the code but on the top of the code. It seems that multi-thread job execution need to have this line to be inserted inside each parallel loop, not outside.

Here is full working code:

#Connect to SQL server
$SQLServer = "XXXXXXXXXXXXXXXXX" #use Server\Instance for named SQL instances
$SQLDBName = "XXX"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security=True;"
$SqlConnection.Open()
    
$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $SqlConnection
    
$DataSet = Invoke-Sqlcmd -Query "SELECT Var_1 ,Var_2 ,Var_3  FROM dbo.Table WHERE Cntry = 'US'"  
    
#Close SQL Connection
$SqlConnection.Close()
    
$Command_Block = {
  Param(
    [string]$Var_1,
    [string]$Var_2,
    [string]$Var_3
  )
        
  # Compile URL link based on input
  $URL = "https://example.com/Prod?cntry=US&Seg=$Var_2&Drth=$Var_1&Prd=$Var_3"
    
  try {
         
    # On the server only TLS1.2 or higher is allowed, by defualt, PS is try to use TLS1.1,
    # which is disabled on the server
    #####    below line was the solution ######
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
    
    # Call REST API GET method, with 3 variables
    $response = Invoke-RestMethod -Uri $URL -Method GET -ContentType "application/json"
            
    # Create File name to be exported
    $FileNamePath = "D:\File_Export\$Var_3.json"
    
    # Store JSON output from API into JSON raw file
    $response | ConvertTo-Json -Depth 100 | Out-File $FileNamePath -Force
  }
  catch {
    $ErrorMessage = "Error occurred while processing URL: $URL `n$($_.Exception.Message)"
    $ErrorFileName = "D:\File_Export\Error_$Var_3.txt"
    $ErrorMessage | Out-File $ErrorFileName -Force
  }
}
    
    
# Start the jobs. Max 4 jobs running simultaneously.
$MaxThreads = 4
$Jobs = @()

foreach ($element in $DataSet) {
  while ($(Get-Job -State Running).Count -ge $MaxThreads) {
    Start-Sleep -Milliseconds 50
  }
  $Jobs += Start-Job `
    -ScriptBlock $Command_Block `
    -ArgumentList  $element.Var_1, $element.Var_2, $element.Var_3
}
    
# Wait for all jobs to finish.
$Jobs | Wait-Job
    
# Get information from each job.
foreach ($job in $Jobs) {
  $info = Receive-Job -Id $job.Id
}
    
# Remove all jobs created.
$Jobs | Remove-Job

Answer 2

When you use Invoke-RestMethod's -OutFile parameter it only writes the response to disk and doesn't return the result anymore:

-OutFile

Saves the response body in the specified output file. Enter a path and filename. If you omit the path, the default is the current location. The name is treated as a literal path. Names that contain brackets ([]) must be enclosed in single quotes (').

So your problem is $response is $null after this line:

$response = Invoke-RestMethod `
    -Uri         "$URL" `
    -Method      "GET" `
    -ContentType "application/json" `
    -OutFile      $FileNamePath

and when it comes to this line later in your code:

$response | ConvertTo-Json -depth 100 | Out-File $FileNamePath

you're blanking out the contents that were written by Invoke-RestMethod

To answer your question as asked, if you want to return the result from Invoke-RestMethod as well as use -OutFile you can add the -PassThru switch:

-PassThru

This parameter is valid only when the OutFile parameter is also used in the command. The intent is to have the results written to the file and to the pipeline.

And the $response will contain the result from Invoke-RestMethod.

However, since you're just overwriting the same file in your code you probably just need to pick one way - either Invoke-RestMehod ... -OutFile $FileNamePath or $response | ... | Out-File $FileNamePath and only do that...