If anyone can understand it is you @JeffModen
When you hit an api you can turn around and feed the json response directly to your staging table without saving to file
You can hit an api and produce a file with cli tools like curl
You dont need ssis u could use powershell python.
You can even call a rest api from within tsql
Let me put together a demo of each options using a public rest api