Link to home
Create AccountLog in
Avatar of Junior Vasquez
Junior VasquezFlag for El Salvador

asked on

Importing JSON Rest API into SQL Using SSIS

This is a general question, i'm trying to figure out how to import a JSON file from a rest API (Link Below) using SSIS. I've never worked with this type of data before, and every where I look, they want you to purchase a tool to do this, we don't have the budget to buy any tools. Any help or examples would be really great. Thanks!

https://opendata.arcgis.com/datasets/aa514416aaf74fdc94748f1e56e7cc8a_0.geojson
Avatar of lcohan
lcohan
Flag of Canada image

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

Open 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.
Avatar of Junior Vasquez

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

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
                                   )