Search code examples
jsonsql-serverssisflat-file

SSIS and JSON Flat Files

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.

Solution

  • 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.