September 29, 2020 at 7:26 am
Hi Guys,
I'm completely new to API's and i was wondering if there is a way of retrieving the data from an API Json format into an SQL database either via SSIS or SSRS or something else.
I know you can use several programs like POSTMAN to run the API and you can also export this data into a Json Format and import into Excel etc but i was hoping to find an automated way of doing this rather than having to export a Json file each day.
Many thanks
September 30, 2020 at 8:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 6, 2020 at 5:18 am
This was removed by the editor as SPAM
November 11, 2020 at 3:08 pm
I'm by no means an API expert, but you want some code that will call the API in a similar way to Postman. This might be a bit of C# or python, or something else like that.
This code then retrieves the JSON string and can either shred it or pass it to a bit of T-SQL for shredding and manipulation using the various JSON functions available.
So, you might have an SSIS package with a script task that calls the API using C#. That script task returns the JSON string from the API call, this is used to set a string variable in SSIS, and the variable is passed to a T-SQL stored proc that shreds the JSON and inserts the results into a table.
Or you might have a stored proc that has some python code as part of the proc, to call the API, retrieve the JSON string, and pass it back as a variable to the main T-SQL code.
Like I say, I'm no expert and haven't done these things myself, but I'm pretty confident that's the kind of approach you want to take. One set of code to call the API and retrieve the JSON string, a second set of code to shred the JSON string into a data set and insert it into the table, with the first set being something other than T-SQL.
November 11, 2020 at 3:27 pm
Receiving JSON and putting it in SQL tables (and the reverse) is pretty much what my job entails these days. It seems like there ought to be an SSIS way to do it? If not and you're open to C#/.NET Core it could be done like that.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply