78 How to import multiple csv files into sql server | Import all CSV files from folder to sql server

hi friends my name is akhil ahmad and in this particular video tutorial i will show you how to import multiple csv files with a different schema into sql server tables using ssis so the agenda of today's video tutorial is how we can import all csv files with a different schema from a folder to the sql server database so let's jump to the demo so this is one of the widely asked questions in ssis like how you can import multiple csv files using a loop into the sql server database so by default if you know that the ssis is very metadata specific and if you are trying to import multiple csv files with different schema then you can't use the foliage loop container along with the data flow task because for the first csv file the data flow task will work but when it will try to load the second file and if that schema of the csc file will change then the data flow tax will fail because you can't change the data type at the run time in the data flow task so that's why we are going to use another alternate method which can import the csv files using a loop into the sql server tables so in the d files location at the moment i got three csv files course.csv student.csv and the teacher.csv so you can have hundreds of csv files and this particular method should work so if i show you the this particular table for example course.csv file and if i open in a textpad editor then you can see that in the column header we have the course id course name and the course duration in years so we have the 10 records of 10 different courses and if i open another file for example student.csv then this particular file contains student id first name last name email and gender this particular file contains the entries for the 1000 students and if i open the another file the teacher.csv so this particular file contains the data for the teachers and it contains 20 records so we have the teacher id first name last name mobile number and the gender so i can close this one so the first thing that you need to do is that according to the csv files you need to create the sql tables in your database so let me open the ssms so if you see here i have created the three tables here like one table for teachers one table for student and one table for the course and according to the header information from the csc file i you created the columns here for example if you check the teacher csv file so let me open this particular file so the first column is the teacher id so that's why i use the teacher id here and now the second column is the first name so we have the first name here now the next column is the last name so we have last name here and now next columns are mobile and gender so we have given the mobile and gender here so we have the exact names whatever names are in the csv files so all the names are here okay so what we are doing here according to the csv file name we will import the data into the same table for example course.csv file will be loaded to the course sql server table and then the student.csv file will be loaded to the student sql server table and then the teacher.csv file will be loaded to the teacher sql server table okay if you check right now so all the three tables are empty and we will be importing the data from the csv files into these three sql server tables so i already written the code and let me explain you the code so let me close this csv file for now and let me open the code here so i will explain you the code like what we are doing here so this is a c-sharp console app and i will show you running this particular app and then i can use the same code inside the ssrs package so i will also show you how you can create an ssis package and execute this particular code inside the ssis package so what we are doing here we have created an instance of the directory info class and we are trying to fetch the details from the d files location and now in this particular line we are getting all the csv files and inserting into the files array of type file info now we have declared two string variables file name and the short file name so the file name will contain the full file name like the file name plus the path and the short file name will contain only the file name like course and now we are running a for each loop here and we are looping through all the files one by one those are situated inside the files array and then we are getting the full file name into the file name variable and then getting the short file name inside the short file name variable and then we are calling this particular method get data from csv file and we are passing the full file name to this particular method so if i go to the definition of this particular method then what we are doing here we are passing the csv file name and we have declared a data table here so we are using the text field parser class here and the delimiter of the csv file is comma so we have used the comma here if your delimiter is something else maybe pipe or something then you can change it here okay and then we have set the hash fields and closed in codes to true so in case if your columns are surrounded by double quotes then it will work and then we are getting the header columns into the call fields array and just creating a string of the header columns and in this particular loop we are actually reading all the data from the csv file and adding it to a csv data data table okay and then in the end we are returning the csv data data table so now as soon as the csv data data table has been populated then we are using this particular method and we are inserting the data from the data table into the sql server table so we are passing the data table and we are passing the short file name and the short file name will actually contain the sql server table name so for example in the full file name we will have the values d files course dot csv and in the short file name we will have the value as course so if you go to the definition of this particular method then what we are doing here that in this particular line we have declared a sql connection here so this is our sql server instance name and this is our database name so our database name is school and we are using the windows authentication here so we have open the connection here then we are using the sql bulk copy class to insert the data and then in the destination table name we have passed the short file name value from here and then we are preparing the column mapping according to the data table and the csv file and then we are writing everything to the sql server table so this is what we are doing here so now let me just put a break point here and show you like how it will work so i can just click on the start button to start the process and i can press on f10 f10 so if you see in the files so there are three files those got assigned to the files array okay course.csv student.csv and teacher.csv and if i press f10 here all right so if you see the file name so the full file name is the da file scores dot csv and the short file name is the course so i can click on f10 and f10 so now the data into the scores sql server table has been inserted and you can go back and check the data in the course table so there should be 10 records yeah so the data has been inserted to the course table but still the teacher and student tables are empty so i can go back and i can press f10 again so now if you check the value of the file name so it is d files student.csv and the short file name should be student and now if i press f10 f10 so now data should be inserted to the student table and 1000 record should be inserted so you can see that 1000 records have been inserted here and the teacher table should be empty as of now and i can go back to the c console app and i can press f10 again so if you check the values now so for the file name it is d files teacher dot csv and the short file name is the teacher and i can press f10 f10 so if you go back so you should have data in the teacher table now yeah so you can see that 20 records have been inserted to the teacher table so i think this is good that you can use the this particular code inside the ssis package so i can click on continue and the process has been completed now suppose if you want to use the same code inside the ssis package then what you can do first of all let me just truncate these three tables so right now all the three tables are empty and now what i can do i can open the blank ss package so this is my blank exercise package that i will use today so i can just drag and drop the script as into the control flow window and i can right click and configure the descriptors so i can click on edit script so that it can open the script editor for me all right so the script editor has been opened up and now what i can do i can just go back to the c-sharp console app and i can copy these functions from here these two functions and i can paste the functions here okay so i have pasted the two functions and it is saying that there is some issue with the text field parser so i can hover my mouse on it and i can click on show potential fixes and i can add this one using microsoft visual basic dot file info so this particular error has gone and now i can scroll down and there is a error here on the sql connection so i can click on show potential fixes and i can add using system.sql client so this particular error has also gone so all the errors are gone now and now what i can do i can just go back to the c-sharp console and i can copy this particular code from the main method and then i can paste inside the main method here so it is giving me some errors for the directory info so i can click on show potential fixes and i can add using system.io so now all the errors are gone and everything seems good here so what i can do i can click on file and exit and i can click on ok so this will save the code for me in the ss package and i can just double check that the tables are empty and now what i can do i can just click on start button so that it should execute the ssis package so that's the size package done fine and i can go back to the ssms and then i can check the data now so you can see the data has been populated to all the three tables and yeah this is good so you can use the same code inside the ss package as well and i will share the code from the script as with you so that you can just download it from the link in the description of the video so i think 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: 8,859

Keywords: How to import multiple csv files into sql server, Import all CSV files from folder to sql server, How do I import multiple CSV files into multiple tables in SSIS?, How do I import multiple CSV files into SQL Server?, How do I import multiple text files into SQL Server using SSIS?, How, how, to, import, multiple, csv, files, into, sql, server, all, from, folder, directory, database, tables, ssis package, sql server, learn ssis

Id: Wi2nBpJY1ag

Length: 11min 11sec (671 seconds)

Published: Wed Aug 10 2022