1

Whats the best way to get JSON flat files into SQL Server using SSIS?

Currently I've tried parsing the data in a script component, but with amount of JSON files I'm parsing (around 120 at a time) it takes upward of 15 minutes to get the data in. I also don't consider this very practical.

Is there a way to combine the powers of SSIS and the OPENJSON command in SQL server? I'm running SQL server 2016 so I'm trying to leverage that command in the hopes that it works faster.

Also, I have no problem getting the JSON data in without losing format. Looks like this:

JSON Example

Is there a way for me to leverage that and get JSON format into a more Normalized format.

AlanPear
  • 737
  • 1
  • 11
  • 32
  • I have always done it in C# and serialized it into a class. – KeithL Feb 15 '18 at 17:27
  • Are you using SSIS at all? If so, are you using a script component as a source or transformation? – AlanPear Feb 15 '18 at 17:28
  • I would use script component as source.and then depending on the JSON complexity push each subclass into it's own dataflow – KeithL Feb 15 '18 at 17:29
  • Perfect! Do you happen to have any links or examples of this? If you could post that below I'll mark it as an answer. – AlanPear Feb 15 '18 at 17:37
  • I cheat and use the ZappySys SSIS PowerPack which has, among other tools, JSON tasks to parse files/api responses, transform JSON data, write to JSON files, and convert parsed JSON into a record set. – digital.aaron Feb 15 '18 at 21:05
  • I'm about to do this since I have been having so much trouble JSON flat files. Is this a free extension? – AlanPear Feb 15 '18 at 21:07
  • No, it costs money. They allow you a 30-day trial, though. – digital.aaron Feb 15 '18 at 21:09

2 Answers2

1

This guy has an example to splitting a JSON string that is in a column that would be a good easy basis.

SSIS Data flow task runs by itself but not as part of package

You would want a class referencing a class if you have subclasses. Kind of like an order class reference a line item class.

In that example, you would have a DF on foreach order and within that a foreach lineitem including the order ID.

I had a good example with Survey Monkey but I can't find it right now.

I actually didn't use data flows with that example and just directly loaded from C#.

Here is the survey monkey class structure i referenced above:

Trouble using all members in a class. Why can I only use the list?

Good luck.

KeithL
  • 5,348
  • 3
  • 19
  • 25
0

Actually figured this out. I bring the files in one at a time, with all the JSON text in a single row. From there I can use the OPENJSON command in SQL Server 2016.

AlanPear
  • 737
  • 1
  • 11
  • 32