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.

No comments:

Post a Comment