Avatar of Junior Vasquez
Junior Vasquez
Flag 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
JSONRESTSSISSQL

Avatar of undefined
Last Comment
Junior Vasquez

8/22/2022 - Mon
lcohan

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.
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
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Junior Vasquez

ASKER

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Junior Vasquez

ASKER
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
                                   )