107 How to load json file in SSIS | Import json file to SQL Server using SSIS

hi friends my name is atul ahmad and in this particular video tutorial i will show you how to load json file in ssis so this is my profile i have 13 plus years of experience in microsoft technologies so the agenda of today's video tutorial is how we can load the json file in ssis so recently i got a question from one of my subscribers sai krishna and he asked me to create a video on this one so i thought to make a video so that it can be helpful for others as well so let's jump to the demo at the moment i don't have a json file but we can download it from internet so let me just go here and normally i download the data from mokaru so i can just type mokaro here and the site mukhara.com will be opened up so i can click on this one so here we can select the columns like id first name last name email gender for example if you want the data for some other columns as well then you can just click on here and here we have 157 types of columns so you can select any column which you want they have actually separated as well like for it you can find the column here for health and you can just type the column name as well for example if you need the column for the email or something so i think i will select all these columns id first name last name email and gender and then the format that we can download it we have different formats here so i will select json in this particular case and then i can just click on maybe i can preview data like how data will look like so it will show you the data so the data will look like something this and then you can click on download data so a json file with 1000 rows will be downloaded so you can see that the file will download it here i can move this file to my d files location from where i normally load the data so i can call this file as email data and i can open this file here in the text page so that you can see like how data looks like this seems good and you can also view the data online as well json [Music] weaver so you can view the data in preview form okay so for example i can copy the data from here 1000 records and i can paste it here in the text and then if you click on the viewer on the left side you can see the data in the tree view form okay so there should be 1000 records here it is start from zero to triple line and if i expand this one zero so you can see the actual data like id first name last name email and gender so this is how you know the data you can see the data in the trivial form so this is fine now let's see how we can actually import this file into the sql server table using ssis so i want to import this file into sql server table so my table is this one email table in the work database as sql server to the 19 instance and i got these columns like id first name last name email and gender and same are the columns in the in my json file as well okay id first name last name we don't have a inbuilt component in ssis which can actually import a json file so we will be using the script component to load the data from the json file so let me open the ssis because we are going to use the script component so an script component can only be used inside the data flow task so first of all let me just drag and drop the data flow task into the control flow window and now i can double click the data flow task i need to use the script component so a script component does not exist in the other sources it exists inside the transformations okay so i can just drag and drop the script component into the data flow task and now it will ask you like how you will want to use the script component like as a source destination or transformations so i will be using it as a source because i want to pull the data from the json file so it will be used as a source so i can click ok and now i can right click on it and need to click on show advanced editor now i need to go to input and output properties and then i can expand the output and under output columns i need to add all the columns like id first name last name email and gender so i will click on add column and the first column i will add is id and then i need to select the data type for this so the data type is integer here so i think that is fine now i can click on add column again so that it can add another column so the second column is first name so i will add first name here and the data type is string for first name so i will select string from here length is 50 so that's good i can add another column which is last name and the data type is string again so i will select string50 now i will add another column which is email and for email i will select the data type string but i will increase the length to 100 because an email id can be lengthy so i think that's fine worker 100 for email and now let me add the last column which is gender and the data type i will use string as well string 50 so that's good now i can click ok now i need to reopen the script component and i need to click on edit script so that the script editor can be opened up all right so the script component has been opened up and now the first thing that we need to do we need to add a reference to system.web.extension so i can right click on the reference and click on add reference and then i can type here system dot web dot extensions so this is the assembly that i need to add i can select this assembly and i can click ok and now you can see that system.web.extensions assembly has been added to my project so that's fine now the next thing is that we need to add the class for the type of data that we have for example we have the email data so we need to add a class here so that we can deserialize this data so now let me go back to the ssis package and i can right click here and click on add new item and then i need to add a class so i can select class here and i will call my class as email and i can click on add so this will add a blank class email class here okay now what i need to do i need to declare the properties here like public integer id and then get set so these are the properties that i need to declare for all the columns those we have like id and then we got first name so like for first name so i will type public string first name get set now similarly i need to do it for last name as well last name and then i need to do it for email and then finally i need to do it for gender as well all right so i have done this one now i can save this one and i can go back to the main clause and now i need to just copy paste the final code here so i can share all code with you and you can use it in your environment as well for your files so you can just type the code here so this is giving us some errors it seems like it is not able to read some of the classes for example it is not able to identify the file class so we need to click on show potential fixes and we can add system.io now it is able to identify this class similarly we need to click on this one java script serializer we can hover our mouse on this one and now we can click on show potential fixes and add this one system.web.script.serialization now we can hover our mouse on list and click on show potential fixes system.collection.generic and now we can hover our mouse on email and click on show potential fixes we can add this name space here okay so i think most of the thing seems good here we can call it this one as obj and maybe you can call as obj 2 and obj 2 obj 2 object okay so this is good i can copy and paste this one so i think everything seems good here now i can give my file name here so let me just copy the file name from here email data dot json so i can paste it here and now i need to give the actual column names here for example this one dot the first column name is id so i can select id here id the next column name is first name so i can select first name here and then i can select first name the second column name is last name so i can select last name from here and now the next column is email so i can type email here email and now the last column is gender so i can type gender here and then gender okay so this is just couple of lines of code and i can share this code with you so you can use it and i think it's pretty simple that you can just use this code and then can access the data from the json so i can just close my script component code here and now i can click on ok so this script component will fetch the data from the json file and now i want to write the data into a sql server table so i will be using an oledb destination here and now i can configure the oledb destination so i can click new to create a new connection to the work database that exists on the sql server to the 19 instance so i already have a connection here so i can click ok and now from data access mode i will select table or view fast load so that it can do a bulk insert and now i will select the email table here and i can click on mappings to make sure that all input columns have been mapped with the destination columns that seems good i can click ok now so i think everything has been configured now and now i can just execute my ssis package which can actually pull data from a json file and it can insert into a sql server table email table so i think it was very quick so i think it took less than a second to load 1000 records so i can just select the data now from your sql server table so you can see that 1000 records have been loaded from a json file to the sql server table so i think yeah that's pretty simple and yeah we can use this one we don't have inbuilt component but i think the option like loading it from a script component that method is also good yeah that's it for today's video thank you guys for watching the video and if you like the video then please click the like button do subscribe to our channel press the bell icon and click on all so that you will be notified every time i upload a new video thank you so much

Channel: Learn SSIS

Views: 15,442

Keywords: How do I import a JSON file into SQL Server?, Can SSIS handle JSON?, How do I load a JSON file?, How do I import JSON files into Visual Studio?, What opens a JSON file?, How do I import JSON into SQL Developer?, Import json file to sql server using SSIS, sql server, sql, etl, json

Id: qDz1Csd_IaI

Length: 11min 0sec (660 seconds)

Published: Thu Mar 10 2022