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!
Avatar of 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:

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

Open in new window

and if you look at more details here:¬†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 will show you just how to do that.
Avatar of Junior Vasquez


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

Avatar of lcohan
Flag of Canada image

Link to home
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.... 

                                   SET @json = ""
                                   SELECT * FROM  
                                    OPENJSON ( @json )  
                                   WITH (   
                                                 --...your JSON document structure here