Reading JSON file and loading into in the table using SSIS Using Data Flow Task

 If we want to perform any transformation in the JSON file data in this case we will use the Data Flow task to load the data into the 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 Data Flow  Task.

In this Post we will see how we will Read the JSON file data and loading into the database.

Taking Data flow task.  

Taking Source as OLEDB Source.  

Providing OLEDB connection manager and selecting Data access mode as Sql command and writing the below code.

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

    

Now taking data conversion to convert the data as per the destination data type.  

Now taking OLEDB destination and doing the column mapping.  

Now package is ready to run

Before running this package see the records in the table.

  

Running this package.  

Package is executed successfully.

See the records in the table.  

Data Loaded successfully.