JSON API - MS SQL Server

[paulrockliffe]

I have an API that I'm able to query in Postman and generate JSON outputs. Ultimately I want the data to end up on my MS SQL Server, the data is going into my PowerBI setup so I can then access it on my phone. I know what I'm doing, just about, with everything apart from how I coax the JSON into the server.

Ideally I want the server to ping the API, check the data for consistency and then add new rows to my existing table, but I'm happy enough to use another piece of software to do that if that's the best approach. The key thing for me is that I can leave it to ping the API overnight, rather than anything manual.

Budget for this is approximately zero as I've managed to avoid spending any money on this so far and it's just a massive bodgy workaround for something.

I did a Google and I found lots of stuff to read, but nothing that really explains what the best approach is and why.

[stuthemong]

A sql server won't do anything unless asked.

Most simply you could set up a cron job / run service that fetches at a defined frequency.

Id just make a python script

S

[paulrockliffe]

I'm comfortable with Google and making things up as I go along rather than any particular language.

Sounds like Python is the way to go and I have it installed, so I might as well start there. Any code snippets gratefully received!

[Crafty_]

you can call an API and then use OPENJSON in newer versions (2016 onwards I think), that allows you to form a select statement of the JSON, which you could then obviously insert in to a table.

https://www.zealousweb.com/calling-rest-api-from-s...

if you wanted it done regularly get a script that works then chuck it in to a sql agent job.

Edit: If that doesn't work you can probably use SSIS to call the api and shove in to a table, again then run on a schedule.

Edited by Crafty_ on Thursday 1st April 18:26

[paulrockliffe]

I've just realised that in Postman I can change the query language to lots of different things, including Python and PowerShell, so I think I have a broadly working script already which is neat.

I think I'm going to park this bit for now though, there's a whole load of development work that I need to do with the data - working out how to build in a load of lookup tables and modifications that run automatically where they can alongside a manual process that will be needed to fill in the gaps.

I'm far happier working in Power BI for working out the details as I can see what's going on with the data and get it all working before recreating the process on the server. And I can more quickly get V1 on my wife's phone, which is the main purpose with this.

Not sure if I can connect to the API directly in Power BI, well I think it's possible it's just tricky as there's a load of messing around with authentication keys, but it's no big deal to run the script once a week and drop the results into a folder where Power BI is already happily mashing all the JSON files together in the interim.