2

I have a need to make a few historical data pulls via REST API call using Powershell. I'm stuck with one of the endpoints where the data volume is over 4 GB. When I run the Powershell script in the Powershell ISE it works as expected. But, When I run the same via SQL Server Integration Services Execute Process Task it's not able to download the JSON payload.

Is there a better way to tackle this? Are there any limitations one should be mindful of?

$Header = @{
    "authorization" = "Bearer $token"
}
#make REST API call
$Parameters = @{
    Method      = "GET"
    Uri         = "https://api.mysite.com/v1/data"
    Headers     = $Header
    ContentType = "application/json"
    Body        = $BodyJson
}
Invoke-RestMethod @Parameters

Error: System.OutOfMemoryException

ITHelpGuy
  • 969
  • 1
  • 15
  • 34
  • Can't you seggregate the data into multiple batches by using some sort of parameter (eg: Start date, end date, range, limit, etc...) Most of the APIS I encountered allow that kind of thing, especially when you need to get such a big amount of data. Personally, I'd do calls in a loop to get a small portion of the data and save it to file as I get it – Sage Pourpre Jun 23 '22 at 16:21
  • 2
    From the context of SSIS, ensure you're running in 64 bit mode vs 32 (as that could be trying to hold the 4GB in memory and 32bit apps are constrained to ~3gigs). Otherwise, why is it failing - what error is being raised? Is it a size issue from SSIS or is it a timeout, or memory constraint etc etc – billinkc Jun 23 '22 at 17:27
  • @billinkc, It is running in 64 bit mode. It does not give me any information about the error as such except for that its failing. – ITHelpGuy Jun 23 '22 at 17:31
  • @SagePourpre, Sure, That is an option but I'm trying to uncover the mystery around the execution of Powershell script in SSIS and no proper error is being surfaced to address the issue. – ITHelpGuy Jun 23 '22 at 17:33
  • Assuming you're running this PS through `Execute Process Task` create two SSIS variables of type string and set them as the .. StandardOuputVariable and ErrorOutputVariable and then put a breakpoint point after the task or use a custom script to messagebox/raise information events with the value of those two variables. It might help surface what is happening here – billinkc Jun 24 '22 at 18:33
  • I was able to find out the cause. I get System.OutOfMemoryException when I run the Powershell in ExecuteProcessTask. Is there a work around other than run the REST endpoint with parameters? – ITHelpGuy Jun 27 '22 at 18:26
  • Hi @SagePourpre, I'm trying to loop over the rest endpoint based on a parameter and append all the chunks of data into one json document. How would I append data to a json document in powershell? – ITHelpGuy Aug 05 '22 at 23:28

0 Answers0