SQL Server json file

Question

I am writing a SQL Server Store Procedure to import a number of json files.

Through data investigation and analysis I have been using OPENROWSET and BULK to query from the Text file, with CROSS APPLY JSON to convert into db columns. (this has worked well).

Now I am at the stage of writing procedures that will run daily, my question is what is the "best practice" for importing json files into SQL Server.

I saw a comment on a (non MS) forum where the advice was OPENROWSET and BULK was for adhoc (quick and dirty), so asking for advice on a robust alternative.

Answer 1

Hi @Adam Newcombe ,

We can use two methods to import jason files into SQL Server if you want to use ssis package.

1.Using Script Component as source in Data Flow Task.
Please refer to Importing JSON Files Using SQL Server Integration Services.

2.Using OLEDB Source and set Data access mode as SQL command.
Please refer to Import JSON data into SQL Server.

Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I could think of using OPENROWSET (BULK) on a regular basis as long as the files lands somewhere they can be reached from SQL Server.

The alternative? Some client-side program that loads them with BCP. Or an SSIS package, if you count SSIS among your skills. (It is not among mine.)