Reading JSON file and loading into in the table using SSIS Using Execute Sql Task

 The JSON file is one of the best options to exchange the data. JSON stands for JavaScript Object Notation.

In this post we see how we will read the JSON file and Parse it and finally, we are loading the data into the database table.

Below is the JSON file. 

This file is having 4 Elements.

Below is the table.

CREATE TABLE Posts_details(                 userID int NULL,                 Id int NULL,                 Post_title varchar(100) NULL,                 Post_body varchar(500) NULL )

In this post we will see how we load the JSON file using Execute SQL Task.

Read here : Read theJSON File and insert those records into the table in SQL SERVER

https://bageshkumarbagi-msbi.blogspot.com/2021/06/read-json-file-and-insert-those-records.html

Now Taking execute sql task and creating the OLEDB connection.  

In the Sql statement we are writing the below Sql script.

declare @json varchar(max); set @json = (SELECT * FROM OPENROWSET (BULK 'H:\SSIS1\Source\Posts.JSON', SINGLE_CLOB) as import)  insert into Posts_details                  (                                 userID ,                                 Id ,                                 Post_title ,                                 Post_body                   )    SELECT userID ,                                 Id ,                                 title ,                                 body      FROM OPENJSON(@json)           WITH (                  userId NVARCHAR(30) ,                  id NVARCHAR(50) ,                  title NVARCHAR(50),                  body NVARCHAR(50)                );

Now our package is ready to run.

Before running this package.  Records in the table.

    

Now running this task. 

Package executed successfully. See the records in the table.    

Data loaded successfully.