August 19, 2018 at 12:58 pm
Comments posted to this topic are about the item Approaches to Import JSON in SSIS (SQL Server 2016+) Part 1
August 20, 2018 at 3:18 am
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
August 21, 2018 at 2:47 am
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
August 21, 2018 at 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!!
August 22, 2018 at 12:39 am
trent_connor - Tuesday, August 21, 2018 2:53 AMUPDATE: 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!
August 27, 2018 at 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
August 28, 2018 at 6:13 pm
sascha.kruening - Monday, August 27, 2018 2:58 AMHello,
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
March 13, 2020 at 12:17 pm
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?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy