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.
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))
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
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
Great post, thanks, essay for me ! We know you need to always be able to provide original ghostwriting papers which are unique to you. Providing work which is copied or simply modified from the original is likely to be spotted as such and can damage your reputation seriously and even get you removed from your studies.
ReplyDelete