December 4, 2017 at 10:06 am
I discovered recently that you can import JSON api data directly in to SQL server 2016 using openjson:
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql
Unfortunately all the search results I have found only show how to do this using either a flat json file or with json data declared in the query. How do I go about importing json api data direct from a http request? The data I'm interested in is the following:
https://api.coinmarketcap.com/v1/ticker/
Documentation: https://coinmarketcap.com/api/
Thanks!
December 4, 2017 at 10:21 am
adamblack - Monday, December 4, 2017 10:06 AMI discovered recently that you can import JSON api data directly in to SQL server 2016 using openjson:
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sqlUnfortunately all the search results I have found only show how to do this using either a flat json file or with json data declared in the query. How do I go about importing json api data direct from a http request? The data I'm interested in is the following:
https://api.coinmarketcap.com/v1/ticker/
Documentation: https://coinmarketcap.com/api/Thanks!
Create a dataflow and add a script component source which will make the HTTP request & capture the response.
From there, you can decide what to do next. Personally, I'd use something like JSON.NET to parse out the columns within the script component source.
December 4, 2017 at 10:46 am
Phil Parkin - Monday, December 4, 2017 10:21 AMadamblack - Monday, December 4, 2017 10:06 AMI discovered recently that you can import JSON api data directly in to SQL server 2016 using openjson:
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sqlUnfortunately all the search results I have found only show how to do this using either a flat json file or with json data declared in the query. How do I go about importing json api data direct from a http request? The data I'm interested in is the following:
https://api.coinmarketcap.com/v1/ticker/
Documentation: https://coinmarketcap.com/api/Thanks!
Create a dataflow and add a script component source which will make the HTTP request & capture the response.
From there, you can decide what to do next. Personally, I'd use something like JSON.NET to parse out the columns within the script component source.
Sorry I dont get what you mean by 'create a dataflow and add a script component'.. any chance you could provide me some example code or advise what i could search for to try and find examples?
December 4, 2017 at 10:57 am
adamblack - Monday, December 4, 2017 10:46 AMPhil Parkin - Monday, December 4, 2017 10:21 AMCreate a dataflow and add a script component source which will make the HTTP request & capture the response.
From there, you can decide what to do next. Personally, I'd use something like JSON.NET to parse out the columns within the script component source.Sorry I dont get what you mean by 'create a dataflow and add a script component'.. any chance you could provide me some example code or advise what i could search for to try and find examples?
Oops, my apologies. For some reason, I thought that this was an SSIS question – my response outlines how it could be done in SSIS. In pure T-SQL, I do not think that there is any way of making an HTTP request.
December 6, 2017 at 12:46 pm
Phil Parkin - Monday, December 4, 2017 10:57 AMadamblack - Monday, December 4, 2017 10:46 AMPhil Parkin - Monday, December 4, 2017 10:21 AMCreate a dataflow and add a script component source which will make the HTTP request & capture the response.
From there, you can decide what to do next. Personally, I'd use something like JSON.NET to parse out the columns within the script component source.Sorry I dont get what you mean by 'create a dataflow and add a script component'.. any chance you could provide me some example code or advise what i could search for to try and find examples?
Oops, my apologies. For some reason, I thought that this was an SSIS question – my response outlines how it could be done in SSIS. In pure T-SQL, I do not think that there is any way of making an HTTP request.
You would have to create a CLR table-valued function that calls the necessary .NET assemblies to do the http request, so it IS possible, but not sure that's a practical way to go. Using SSIS is considerably easier.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply