2

What is the standard way of loading a bulk of JSON data from a file into a database table in an SQL Server version less than 2016?

I am aware of OPENJSON() function that was introduced in SQL Server 2016. However, I am limited to use SQL Server 2014.

The preferable way of doing this is to import data using SQL Server Integration Services (SSIS), but there is no in-built connector or a source component to start with.

I tried using SSIS JSON Component for this purpose. Apparently, it is not good from performance point of view.

Is there any other way of bulk loading JSON into SQL?

Akshay Rane
  • 403
  • 4
  • 13
  • My answer to this question should apply here, too: http://stackoverflow.com/questions/37600180/extract-labels-from-serialized-array-using-sql/37600784#37600784 – Philip Kelley Sep 01 '16 at 13:52
  • I did have a look at the parseJSON function on simple-talk.com, I didn't find it ideal for loading bulk data. I should probably try it out and keep an eye on the performance. – Akshay Rane Sep 01 '16 at 14:04
  • Phil Factor's parser looked like a T-SQL monster. It might work, but it looked kludge as heck. The main link I referenced is a CRL assembly written by someone who went a step further than Phil. – Philip Kelley Sep 02 '16 at 14:14

1 Answers1

2

I've done this in SSIS 2012 / SQL Server 2012. The requirement was to consume a RESTful API with various endpoints that returned json.

  1. In your Data Flow, create a Script Component. Most likely it will be of Type = Source since you will be generating OutPuts with Output Columns from it.

enter image description here

  1. Create the various OutPuts and OutPut columns for the various fields / data items you will extract from the JSON. Usually this is several tables as shown below.

enter image description here

  1. In the Scripting Component's CreateNewOutputRows() override method, Deserialize the JSON data into C# classes. I use http://json2csharp.com/ to stub the classes and then fix it if need in special cases, like if the json has ID values for key, instead of keyname and such. I use Newtonsoft.Json to deserialize the json into the class instances / Lists etc. Add rows to the relevant outputbuffer you setup in Step 2.

  2. Connect the Outputs from the Scripting Component to the various destination tables.

That's it! You are done.

Shiva
  • 20,575
  • 14
  • 82
  • 112