143 How to import multiple excel files into sql server using ssis

hi friends my name is Akil Ahmed and in this particular video tutorial I will show you how to import multiple Excel files into SQL server using ssis so the agenda of today's video tutorial is how we can input multiple Excel file into SQL server and then we will also implement the auditing as well so for example whatever file will be imported to the SQL Server table we will lock the file path along with the number of Records inserted and then we will also write the code that a file if it is already loaded then it should not be loaded twice so let's jump to the demo in my D files location as of now I got two Excel files here but even if you will put more Excel files here so all files will be imported so we will be using a footage Loop in the ssis which can actually import all files in a folder so if I show you the data in the Excel file so it's employee Excel file and as of now it contains only 10 records the first file contains 10 records okay and we have ID first name last name email and gender and if I show show you the another Excel file it also contains 10 records but here the ID starts from 11 to 20 and in the first file the ID starts from 1 to 10 okay this is the change even the data is also different okay so now let me try to import these files into the SQL Server table so is equal server to the 19 instance and I will be importing the data to the test database here so this is my blank SSS package which I will be using today and because you want to look through multiple Excel files so we will be using a 4ish loop container here so I can just drag and drop the for each Loop container into the control flow window and then we can configure the 4ish loop container but before configuring the for each Loop container we need to make an ssis variable here and we can call the variable as file path because when the loop will iterate so it will get the file path and we need to store it in an ssis variable so we can create the ssis variable within a file path and the data type we can give as string so we need to give an initial value to the file path so I can maybe select the value this one D files employee and I can provide the value here so our variables has been created now I can close this window and now I can configure the for each Loop container here now I can go to the collection and from the innovator type I can select the forage file animator because the file enumerator can be iterated through multiple files in a folder now from the folder option I can select the folder from which folder you want to iterate the files to so our files are situated inside the files location so I will select this location here and what type of file you want to import so I want to import XLS X Type file so I can provide the extension here so it will only iterate through these files now I can go to the variable mapping and I can select the variable file path to which I want to assign the file path to so for each iteration the current Excel file path will be assigned to the file path exercise variable so I can click on OK R4 slope container has been configured now we can just drag and drop the data flow task into the 4ish loop container because data flow task will be used to import the data I can configure the data flow task because our source is a Excel file so we can just drag and drop the Excel Source into the data flow task and then we can configure the Excel source I can click new to create a new Excel Connection Manager and I can browse the file I can browse any file as of now I can click on OK from the name of the Excel sheet I can select the Excel sheet name from here so the Excel sheet name is sheet1 I can go to the column so these are the columns those will be imported and I can click on ok now we can use an oledb destination to import the data to a SQL Server table and connect the Excel source with the oledb destination I can right click and configure the oledb destination click new to create a new oledb Connection Manager we already have a connection manager which can connect to the test database on the SQL Server 2019 instance so I will select this connection manager from here click ok from data access mode I will select table or a fast load and because as of now the table is not there in the database so I can click new to create a new table and I can call my table as employee and I can click on OK if I go to the column mappings so all the input columns have been with the destination column so this is good and I can click on OK so our ssis package is almost ready when the loop will run so it will import the data from the Excel file to the SQL Server table the only issue as of now is that the Excel Connection Manager is hard coded and it should be dynamic if you go to the property of the Excel Connection Manager and if you look at the Excel file Path property so right now the value is D files employee.xlsx but this value it should come from the ssis variable file path because the file path value will change during the iteration of the loop so we can set this value dynamically by clicking on the expressions and from the property we can select the Excel file Path property and then from the Expressions we can just drag and drop the file path ssis variable so now what will happen when the loop will run the value of the file path will change and similarly the Excel Connection Manager will also change now you can see an f x sign before the connection manager which means that this connection is now Dynamic okay so our SSS packet is ready if you click on start so it should import the data from both the files into the SQL Server table the package can yeah it imported both the files so that's fine now I can go back to the ssms and I can just see the employee tables as of now you can't see the employee because it was not there now let me refresh the tables and now you can see an employee table here so let me select the data from this employee table there should be 20 records yeah so these 10 records are imported from one file and then the rest 10 records imported from another file so this is working fine yeah so this was the very basic package which can import the data from Excel file multiple Excel file into a SQL Server table now majority of that time we also want to know like how many files Got Loaded how many records got loaded and when the files were loaded okay if you want to know all the details means you want to do the auditing as well then we can create an auditing table okay so I already written some code here so that this is the clear table statement for the audit log SQL Server table so it will contain the ID as an identity then the package name file loaded which file loaded how many records inserted and when the data was loaded what I can do I can execute this query so it should create the audit log table now so what we can do we can actually insert a record into the audit log for every file loaded so we can write an insert query here insert into audit log select ID will be identity so we can escape it our package name you can write the package name anything uh our package name is package.dtsx normally you need to give the package name properly so that you can just know like after looking at the fax name like what the package actually works maybe like load employee data kind of thing and then the file loaded so we need to provide the file path here okay and then number of Records inserted and the current date time this type of query should work so I can just copy the query from here okay and now I can go back to the ssis package and I can use an execute SQL task here okay and I can connect the data flow task with the execute SQL task there is one important thing that because we want to know like how many records Got Loaded by a file so we need to Define an ssis variable maybe type of type integer like records imported okay and the data type will be in 32 so that's fine now I can go back to the data flow task and here we need to use a row count transformation and we can just connect the Excel source with the root count transformation and select the SSS variable here records imported and then connect the row count transformation with the oledb destination now that's fine now when the SSS package will run now for each iteration the number of Records loaded the count will be in inserted to this particular ssis variable so now I can just rename it like do auditing and then I can right click and configure this one Connection Manager is SQL 0 to 19 test database and because we will be using two ssis variables the file path and the records imported into this SQL query so we can use the Expressions here so I can go to the expression and the from the property I can use the SQL statement property okay and then I can write the insert query here we need to put double quote paste the query and the double quote and I will share this query with you so that in case if you want to use it then you can use it in your SSS package so the package name is hardcoded as of now but we can get the package name dynamically so I can put double quote plus plus double quote and then we can just drag and drop the package name from the system variable so there is a system variable package name so we can get the package name from there if you want to put it hard coded then it's up to you that's fine and now we need to get the file path so for the file path we can put double quote plus plus double quote and remember all these things like package name file path because those are of type strings so we need to use a single code as well in the insert query you know now we can get the file path from this variable file path now the only ssis variable that is remaining to be mapped is the records imported but as of now if you will click on the event expression then you can see that this query seems good here so you can copy the inside query from here and maybe you can just paste it here and if you try to execute this one so this is working fine so it means that it will work fine when you will execute the package as well it's a good practice that you can test it until you are learning the expression language now the only thing that needs to be set is the records imported so as of now it is zero so we can put double quote plus plus double quote and then we can drag and drop the records imported ssis variable here okay for rest of the two variables because they were of type strings so we don't need to Typecast them but this is of type in 32 do so we need to type because the n32 variable to the string so we can just drag and drop DT underscore wstr and we can set the length for this one so maybe I can give the length as 50 and I can click on evaluate expression click ok ok ok so now we have implemented the auditing in our ssis package and now I can go back to the ssms and if I check the data now so the data is already there so maybe I can just truncate my table here and maybe you can put the trunk it query in the ssis package as well maybe just before the for each Loop container so the table has been truncated and as of now if I check the data from the audit log table so the audit log table I think it should be empty or it has some data yeah it has one record so what I can do I can just rerun this Square so it will drop and recreate this table so as of now the audit log table is also empty now let me execute the SSS package and it should import the data so the pack is ran fine and now I can check the data now so from the employees table it should have 20 records and if you look at the audit log table so it has two entries like first it has loaded the employee table 10 records got loaded this is the time then it imported another file 10 records loaded this is the time so this is how the auditing can be done and now if you want to make sure that if you have already loaded a file then the same file should not be loaded again so for example if I rerun the SSH packet then it should not import the file again if you want to implement that kind of thing so that's pretty straightforward and what you can do you can create maybe another table to keep the number of files those are already loaded so when the package will run first it will check if the file is already loaded so if the file is already loaded then you don't need to load the file again and if file is not already loaded then you can load the file so maybe I can call my table as file details or maybe files loaded and yeah and I can put it here I can have the column as package name and the file loaded I think the two columns are fine that's it yeah if you want to keep the time as well like when the file was loaded then maybe we can keep the date time as well so that we know like when the file was loaded so I can execute this query and the files loaded got created here all right so when we will insert the data so we will write a query like insert into files loaded select package name which will like package Dot dtsx and the file path and the third one will be a time when the file was loaded so this is a simple insert query so I can copy the insert query from here I can go back to the ssis package so in the do auditing we can just put the insert query here so I can open this query in the expression and I can put a go keyword here and then I can place the query for the ssis package name I can copy the you know value from here because I already use the variable here same variable I can use here as well okay and for the file path as well I can copy the same query file path because this is the same variable that I am using so I can put it here as well and get it is fine so I have written my code here I can click on OK OK so now when package will run when file will be loaded so it will insert one record to the audit log and another records to the files loaded table okay now what we want to do is that before loading the file we need to check if the file is already loaded or not so maybe I can use it execute SQL task here yeah I think that's what you need to use and in the execute SQL task we can configure this one but before like configuring this one we need to use an ssis variable here so we can create an ssis variable like files loaded like how many files loaded you can think of this one like if the number of files loaded are zero it means that the file is not loaded and if the count is more than 0 then it means a file has been loaded so we can call it like get number of files loaded okay and what we can do we can write a select query here count star from files loaded where package name package.dt SX and file loaded equal to the file name so if the file is not loaded then it will return the count as 0 otherwise it will return account 1. or number of count like how many files are loaded so I can copy this query from here I can configure this one so I can right click and configure this one Connection Manager I will select The Connection Manager I will go to the expressions and here I will select SQL statement source and then I can put double quote paste the query now from the file file loaded we need to use the file path SSS variable so I can put double code plus plus double quote then I can drag and drop the file path ssis variable here because it contains the file path for this one like package name whatever package name you have inserted that you can use it so maybe you can use from the system variable if you want otherwise you can hardcode this values when that's totally fine okay so that's fine now I can click on okay okay if I go to the general and then we from the result set we need to select the single Row from now we can go to the result set and we under the result set name we can select the value as 0 and we need to assign this value to the files loaded like how many files you did so I can click on ok now what will happen once you will get the value of the files loaded then so after the file loaded and before the data flow task we can just configure the residence constant so we can click on edit and from the evaluation operation we will select expression and constants so we can click on acceleration now what we can do we can just drag and drop the files loaded here means if number of files loaded are equal to 0 means if no file is loaded then we can just load the file so we can click on OK so now if the value of the files loaded will be 0 then this task will run and rest of the tasks will run otherwise they won't run okay so now our ssis packet is completely ready and now I can go back here and I can truncate the employee table and I can leave the audit log as it is and now let me rerun the ssis package so you can see that like how it will work all right so the pack is ran fine and we can check the data from the files loaded as well actually so we should have 20 records in the employee table that's fine now we can check the audit log so two more files got loaded here I can check the files loaded here file so two files are loaded here so this is totally fine at the first time you know maybe I can put this in the end so this is completely fine for records in first table two records in second table now if I will rename the SSI species then what will happen none of the file will be loaded because both the files are already loaded so they won't be loaded again you know you can see that now you can run the package and number of time and no new file will be loaded and now suppose if you will put a new file then the new file will be loaded okay so maybe I can show you like putting a new file so suppose let me copy the second file and paste it here and let me call the second file as uh third file I can call it anything you know third file now if I will resume the ssis package then this third file will be loaded because it will think that the this is a new file so that's why it will import the file and it will import only one file so if I go back and if I check the audit log so you can see that only one file Got Loaded which was the third file and this file will have an entry in the file loaded as well third file and I think now we should have 30 records yeah so I think that's it for today's video and I will share all the files used and all the SQL scripts used and the career table statement with you so you can download it from the link in the description of the 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 also that you will be notified include a new video thank you so much

Channel: Learn SSIS

Views: 16,450

Keywords: How to import multiple excel files into sql server using ssis, How do I import multiple Excel spreadsheets into SQL Server using SSIS?, How do I import multiple Excel files into different SSIS packages?, How do you bulk insert Excel file in SQL Server?, How do I load multiple files in SSIS?, How, how, to, import, multiple, excel, files, into, sql, server, sql server, using, ssis, SSIS, learn ssis, sql server integration services, msbi

Id: TH9ptAkCHNA

Length: 18min 42sec (1122 seconds)

Published: Thu Nov 03 2022