TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
Issue: How to Create Self Hosted IR and Load Data From On-Prem to Azure Cloud - Azure Data Factory Tutorial 2021.
In this blog post we are going to learn how to create Self Hosted IR and load data from On-Prem to azure cloud azure data factory tutorial 2021 step by step.
Create & configure A self-hosted integration runtime :
Open azure data factory & click on manage tab, click on integration runtime and click on new:
Click On azure , self-hosted insert information and click create:
Fig-2 : Information column name & description
Click on the download & install integration runtime.
Fig- 3 Download & install integration runtime
Open integration runtime & copy keys from azure data factory which is shown in below picture
Fig- 4: Copy the keys for self-hosted integration runtime
Paste the copied key and click on register & then click finsh.
Fig- 5: Insert copied keys for registration
Create a pipeline give the name to our pipeline and click & drag copy activity :
Fig-6 : perform copy activity
Click on source + new and select sql server & click continue
Fig- 7: Create new source for copy activity
give name , crete new link server, select server name, select self hosted IR, give data base name + tb user + password and click test connection.
Fig- 8: Create new linked service
Click on open
Click on preview
Click on azure blob storage + select csv file + set properties insert name & link service + create output connection & click ok + debug and the file will be generated
Fig-9 : Create new blob storage step by step
How To Copy File from local folder to Cloud
Go to the drive where the folder is located right click on the folder go to properties and click sharing and then click share and provide the permission to the user, and click share.
Fig- 10: How To Copy File from local folder to Cloud
copy shared link :
Go to azure data factory and create new pipeline, drag copy data activity and click on source, then click on new then select file system
Fig- 11: create new pipeline
Select binary and click continue
Name the file and create new linked service
In the link service select server name, then select self hosted IR, then host then user name & Password then click on test connection and click create
Click open
and browse the required file and click ok
go to pipeline and click sink click on Blob Storage Then Click on Binary then Select Azure Blob Storage Link and Select the output folder and click ok
Click ok Debug
As you can see in below picture task is completed:
How to Create Self Hosted IR and Load Data From On-Prem to Azure Cloud - Azure Data Factory Tutorial
Issue : How to Load Multiple JSON Files to SQL Server Table with File Name In SSIS - SSIS Tutorial 2021
In this blog post we are going to learn how to load Multiple JSON file to SQL Server Table with File Names in SSIS. I have generated couple of JSON Files online so we can use them for our demo.
Here is snapshot of one of JSON file we have used in demo.
Fig 1- Load JSON files to SQL Server Table in SSIS Package - Sample JSON File
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 - How to Load JSON File in SQL Server by using TSQL
By using OPENROWSET function we have imported the JSON Files in @JSON variable.
Fig 3 - How to use OPENROWSET to import JSON Files in variable in TSQL
OPENJSON is table value function that can convert JSON data into columns and rows. As you can see below I have use OPENJSON and provided our variable @JSON as input so we can get data in tabular format.
Fig 4- Get tabular data by using OPENJSON Function in TSQL
Next step we have to describe the list of columns those we would like to get from our JSON data. If you do not want to read all column data, you can provide the list of only required columns.
Fig5 - Get desired column from JSON file by using OPENJSON Table function in TSQL
To load our sample JSON files, we need a table in which we can load our JSON file data.
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 6- Create table by using TSQL for JSON File load
If you want to write the data to some table, you can provide table name. Make sure table has the same columns which your OPENJSON table
Fig 7 - Insert data into table from JSON files by using TSQL
as you can see in below picture 1000 rows are inserted.
Fig 8- How to Insert JSON Files into SQL Server Table by using TSQL
After all the scripts tested in TSQL to load JSON file to SQL Server Table, we are going to create SSIS Package to load multiple JSON Files to same table that we created above.
Open Visual Studio & Click on Packages + New SSIS Package
Fig 9- Load JSON Files to SQL Server Table by using SSIS Package - Create New SSIS Package
In the new SSIS Package we need Foreach Loop Container to get list of JSON files from Folder
Fig 10 - Get list of JSON Files by using Foreach Loop Container in SSIS Package
In Foreach Loop Container we have to select the type & Path of our JSON Files
Fig 11 - Choose only JSON files from folder by using Foreach Loop Container
Next Click on Variable Mappings & Input Details
Fig 12 -Save the file name into variable in Foreach Loop Container in SSIS Package
Bring Execute SQL Task and configure OLEDB Connection.
Fig 13- Use Execute SQL Task in SSIS Package to Load JSON files in SQL Server Table
You will be choosing your Server Name and authentication method to connect to your SQL Server.
Choose Server Name and Database Name for connection Manager.
Step 5: Test Connection Succeeded.
Next Click on Expressions Click on the Property & select SQL Statement source then Click on Expression & Copy Paste The Below Given Command in Expression and add the file Path Which is created earlier.
"Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK "++", SINGLE_CLOB) import
insert into dbo.SSISJson
SELECT * FROM '"++"' as filename FROM OPENJSON @JSON
WITH
(
[ID] INT,
[first_name] varchar(100),
[last_name] varchar(100),
[email] varchar(100),
[gender] varchar(20),
[ip_address] varchar(10)
)"
Once the expressions are evaluated correctly. We are all set to test our SSIS Package. Truncate your SQL Server Table and then execute SSIS Package to load multiple JSON files
Fig 14 - Load multiple JSON files to SQL Server table by using Execute SQL Task in SSIS Package
Go to SSMS and then execute Select query for table to confirm if data is loaded successfully.
Fig 16- Load More than one JSON File to SQL Server table by using SSIS Package
If you still need any help and have missed any steps in creating SSIS Package, feel free to check video demo that have step by step approach how to load multiple JSON files to SQL Server table.
Video Demo: How to Load Multiple Json Files to SQL Server Table with File Name In SSIS
How to Load JSON File to SQL Server Table by using TSQL in SSMS - TSQL Tutorial
In this blog post, we are going to learn how we can load JSON file to SQL Server table by using TSQL.
I have generated below JSON file 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 Highlighted in this picture.
Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'c:\MyJsonFile.json', SINGLE_CLOB) import
insert into dbo.SSISJson
SELECT * FROM OPENJSON @JSON)
WITH
(
[ID] INT,
[first_name] varchar(100),
[last_name] varchar(100),
[email] varchar(100),
[gender] varchar(20),
[ip_address] varchar(10)
)
By using OPENROWSET function we have imported the JSON Files in @JSON variable.
OPENJSON is table value function that can convert JSON data into columns and rows. As you can see below I have use OPENJSON and provided our variable @JSON as input so we can get data in tabular format.
Next step we have to describe the list of columns those we would like to get from our JSON data. If you do not want to read all column data, you can provide the list of only required columns.
If you want to Insert this data in some table just follow the steps:
(SELECT * into dbo.JsonTestTable)
Once you execute the TSQL statement, all the data from JSON will be inserted into table.
Video Demo : How to load Json file to SQL Server Table by using TSQL