sql – SSIS and multiple API calls, a better way?


I am looking for some guidance on my solution as I am convinced there must be a better way.

The below is my setup…

SSIS Package

API 1 – This calculates invoice totals and payments

API 2 – This takes the above invoice totals and adds them into our payroll system, e.g. Sage

So the data flow for this is as below:

  1. SSIS Package makes a web request to API 1
  2. API 1 Performs some calculations does some CRUD actions then sends a JSON payload to API 2
  3. API 2 takes the payload and performs some small code calculations but ultimately inserts it into the payroll system

I am having some issues using this approach highlighted in this thread https://stackoverflow.com/questions/63942271/rest-sharp-post-error-the-underlying-connection-was-closed-a-connection-that

In short the chain of API calls seems to cause problems e.g. a web request is made whilst one of the API’s is still trying to process the last request.

Is there a better way to achieve this without the need for so many seperate API calls.