How to Load Multiple JSON Files to SQL Server Table by using Data Flow Task in SSIS Package - SSIS Tutorial 2021

 Issue : How to Load Multiple JSON Files to SQL Server Table by using Data Flow Task in SSIS Package - SSIS Tutorial 2021

In this blog we are going to learn how to load multiple JSON files to SQL server table by using data flow task in SSIS package, I have generated couple of JSON files online so we can use for our demo.

Fig-1: JSON file snapshot

Open SQL server management studio (SSMS) and use the script I have provided below, first of all we have to declare the variable which is "Declare @JSON varchar (max), which I have Shown in this picture.

Declare @JSON varchar(max)

SELECT @JSON=BulkColumn

FROM OPENROWSET (BULK 'C:\MyJsonFile.json', SINGLE_CLOB) import

SELECT * FROM OPENJSON @JSON)

WITH

(

 [ID] INT,

 [first_name] varchar(100),

 [last_name] varchar(100),

 [email] varchar(100),

 [gender] nvarchar(20),

 [ip_address] varchar(10)

)

Fig-2 : TSQL script

Create table by using below command:

create table dbo.SSISJson(

[id] int,

    [first_name] varchar(100),

    last_name] varchar(100)

    [email] varchar(100),

    [gender] nvarchar(20)

    [ip_address] varchar(10),

    filename varchar (1000))

Fig-3: Create table TSQL script

Open visual studio and create new SSIS package & open foreach loop to read the list of the files:

Fig-4: Creating new SSIS package

In foreach loop container we have to select the type &  path of our JSON files

Fig-5: Foreach loop editor.

Create the all variables that we are going to use inside the data flow:

Open expressions window

Fig-6: Creating Variables

 Input the Below Command in Expression:

Declare @JSON varchar(max)

SELECT @JSON=BulkColumn

FROM OPENROWSET (BULK 'C:\MyJsonFile.json', SINGLE_CLOB) import

SELECT * FROM OPENJSON @JSON)

WITH

(

 [ID] INT,

 [first_name] varchar(100),

 [last_name] varchar(100),

 [email] varchar(100),

 [gender] nvarchar(20),

 [ip_address] varchar(10)

)

Change the path delete path & insert "++" and drag the path variable, and evaluate expressions. click ok and back to the main window.

Fig-7: Creating Variables

Bring the data flow & inside data flow we are going to use OLE DB source

Fig-8: Using OLE DB source.

Double click on OLE DB source and make the connection with the database where we need to load and click ok.

Fig-9: Creating connection with the database

Drag derived column double click on derived column and insert file name, derived column, expressions, & data type and click ok., 

Fig-10: Making derived column

Bring the destination ,double click on OLE DB destination, , select the table and click ok. 

Fig-11: Adding OLE DB destination.

Execute the package and go back to SSMS and verify the file 

Video Demo: How to load JSON files to SQL server table by using data flow task in SSIS package - SSIS tutorial 2021.