Approaches to Import JSON in SSIS (SQL Server 2016+) Part 1

Question

Approaches to Import JSON in SSIS (SQL Server 2016+) Part 1

Nikolai Basov

SSC Enthusiast

Points: 129
More actions
August 19, 2018 at 12:58 pm

#378287

Comments posted to this topic are about the item Approaches to Import JSON in SSIS (SQL Server 2016+) Part 1

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Deepak Sharma-2311 SSC Eights! Points: 993 More actions · Answer 1

I recently came across a situation where we need to create a json and save the result in a file. We were using SQL server 2017. We have created json from SQL server using FOR JSON clause and use SSIS to export the data of the SQL code in a file.
We have used SSIS because JSON was too big and when we save the result of sql query in a file, SQL breaks the json tags which result json invalid.
SSIS is the best option in that case.

Deepak Kumar Sharma

DC_07 SSC Enthusiast Points: 171 More actions · Answer 2

Hi, thanks for this article. It comes at the perfect time for me as I'm starting a project that imports json files to SQL I downloaded your solution to test and I'm surprised at the times you give. For approach 1 (with PeopleLarge.json) I get 2min07sec for SQL and for json.net it takes 3 seconds!! why such a difference? (i executed multiple times to confirm the result)
I tried an Execute SQL task (with a modified query) to bulkload as I only need to get the data into SQL and this took 5 seconds, very similar to json.net.
Here's the query I used for the Execute SQL task:

DECLARE @request VARCHAR(MAX)

SELECT @request = BulkColumn FROM OPENROWSET(BULK'C:\PeopleLarge.json', SINGLE_BLOB) JSON;

insert into [Test].[dbo].[Person]([PersonId_SK],[FirstName],[LastName],[BirthDate])

SELECT

Id, FirstName, LastName, BirthDate

FROM OPENJSON (@request, '$')

WITH (

Id INT,

FirstName VARCHAR(50),

LastName VARCHAR(10),

BirthDate DATETIME

) AS p

DC_07 SSC Enthusiast Points: 171 More actions · Answer 3

UPDATE: I created the SQL package from scratch and now takes 6 seconds (not sure what happened before)
I'm going to test the json.net with my project!!

Nikolai Basov SSC Enthusiast Points: 129 More actions · Answer 4

trent_connor - Tuesday, August 21, 2018 2:53 AM
UPDATE: I created the SQL package from scratch and now takes 6 seconds (not sure what happened before)
I'm going to test the json.net with my project!!

Hi trent_connor, not sure why the downloaded version did not work well for you, but I'm glad that you solved the problem! Thanks for sharing!

sascha.kruening Grasshopper Points: 23 More actions · Answer 5

Hello,
how do you deal with more complex or interleaved JSON like

"person":{ "name":"name1", "age":32 "email":[ "email1", "email2"], "address":[ {"city":"city1", "postcode":"postcode1"},{"city":"city2", "postcode":"postcode2"}] }
Thanks
Sascha

Nikolai Basov SSC Enthusiast Points: 129 More actions · Answer 6

sascha.kruening - Monday, August 27, 2018 2:58 AM
Hello,
how do you deal with more complex or interleaved JSON like
"person":{ "name":"name1", "age":32 "email":[ "email1", "email2"], "address":[ {"city":"city1", "postcode":"postcode1"},{"city":"city2", "postcode":"postcode2"}] }
Thanks
Sascha

Hello Sascha,

I like how such cases could be processed in C# + Json.NET. You'd just need to create all the necessary classes and the library will do the rest. After that you can distribute the data among your outputs as you wish:

void Main() { var json = @"[{""Person"":{ ""name"":""name1"", ""age"":32, ""email"":[ ""email1"", ""email2""], ""address"":[ {""city"":""city1"", ""postcode"":""postcode1""},{ ""city"":""city2"", ""postcode"":""postcode2""}] }}]"; var records = JsonConvert.DeserializeObject<List<Record>>(json); records.Dump(); }

public class Record { public Person person { get; set; } }

public class Person { public string name { get; set; } public int age { get; set; } public string[] email { get; set; } public Address[] address { get; set; } }

public class Address { public string city { get; set; } public string postcode { get; set; } }

In case you are trying to achieve something similar in SQL, a solution depends on your design. I put here an example of how to multiply data from the parents's nodes and join it with the nested data:

DECLARE @json NVARCHAR(MAX) = '{"Person":{ "name":"name1", "age":32, "email":[ "email1", "email2"], "address":[ {"city":"city1", "postcode":"postcode1"},{ "city":"city2", "postcode":"postcode2"}] }}'

SELECT p.name, p.age, e.value, a.city, a.postcode FROM OPENJSON(@json, '$.Person') WITH ( name VARCHAR(50), age INT, email NVARCHAR(MAX) AS JSON, address NVARCHAR(MAX) AS JSON ) AS p CROSS APPLY OPENJSON(p.email) AS e CROSS APPLY OPENJSON(p.address) WITH ( city VARCHAR(30), postcode VARCHAR(50) ) AS a

Rob MacNaughton Valued Member Points: 71 More actions · Answer 7

Most often I find the Json in another database and as it is of a type that can't be truncated so ssis defaults to blob . This means row by row not batch. What is the method to extract as batch for speed?