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

  • Nikolai Basov

    SSC Enthusiast

    Points: 129

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

  • Deepak Sharma-2311

    SSC Eights!

    Points: 993

    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

    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

    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

    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

    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

    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

    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 8 (of 8 total)

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