Importing JSON Rest API into SQL Using SSIS

Essentially you can import JSON data/docs/files by using a OPENROWSET command like in the sample code below:

SELECT BulkColumn
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j;

--or like:
DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\file-location\my-data.json', SINGLE_CLOB) 
AS j

Select allOpen in new window

and if you look at more details here: https://www.pauric.blog/How-To-Import-JSON-To-SQL-Server/ they actually show exactly how to import "...the JSON is from an API call for a set of land parcels from a virtual reality platform (to make things interesting 🦕):"

If you really want to use SSIS to perform the import then I believe the link https://www.cdata.com/kb/tech/json-ssis-task-import-2008.rst will show you just how to do that.

I actually need to get the data from the rest API, not from a drive, also the dba's don't allow us to get external files

Will this SSIS Solution work with JSON ? I'll try and tweak it and let you know. 

I found out we can use open JSON. In this solution, where can i find the JSON structure ? I assume it must be like an .xsd file which defines the document. However, since i've not worked with JSON at all, i should probably find that document on opendata.... 

DECLARE @json NVARCHAR(MAX)
                                   SET @json = "https://opendata.arcgis.com/datasets/aa514416aaf74fdc94748f1e56e7cc8a_0.geojson"
                                   
                                   SELECT * FROM  
                                    OPENJSON ( @json )  
                                   WITH (   
                                                 --...your JSON document structure here
                                   )