SSIS Tutorial Part 20-Load Multiple Sheets from Multiple Excel Files to Different SQL Server Tables

Brothers with today we are going to learn how to load different sheets to different SQL Server tables from multiple Excel files in ssis package so what I have here for you two files they Excel files and they have multiple sheets so each one has a different sheet one one is called uh let's say first one is called customer and it has different columns it has ID first name last name address phone number region code and join key in the second sheet what I have customer short that's the name of the sheet and I have only first name and last name and there could be possibilities we will have more sheets available to us but in my example I'm only going to perform this demo for two of them and you can have maybe three sheets or four sheets depending on your requirement but the way we will create the ssis package will be the same so this this is the first file I have it the second file is exactly like this one but the data is different so the sheet name will stay the same we have customer sheet here and then we have customer short but data is a different so we have multiple files with multiple sheets and each on each of the file the sheets are exactly the same and we need to load these one to SQL Server table now what I have here on SQL server side I have created two tables one is customer so I took a look on the customer sheet and then I created this tables uh uh one First Column is ID then first name last name address and all that so it is exactly same uh like uh our customer sheet the second one I have created is called customer short so this table has only two columns so first name and last name now let's go go to the ssdt your bids and create our ssis package so we are in the solution right now right click on the ssis packages and say new ssis packages here to load this one uh give me a second and make sure the recording is working so it's working it's early morning 4:00 and around 5:00 I don't want to mess up that if the recording do working you want to come back after 30 minute or recording didn't work okay so anyways so first thing we need to load the data so what we need we need data flow task and here in the data flow task what I'm going to do I'm going to read the data from the Excel file actually I will be reading the data from Excel file but we'll be reading multiple sheets so anyways bring the Excel source here let's make it a little large open it now create a connection to any of the file as of now as we have multiple files we have to do some trick but as uh first let's create connection for one of that so we browse it and then we go to the input folder and create connection for any and we have a 2 U Microsoft Excel 2007 that's what I have I have the Row first row has a column name that's correct for me so I hit okay next part is the select the sheeter so here I'm going to select customer and uh I have columns looks good here now I need to load the data to the SQL Server table I will be using o DB destination here so I connect Excel source to O DB destination I'm going to make a connection here and uh let's create let me go back and delete the old one I have the connection already I'm going to delete and create a new one so I can show you how to do it so once you click here you will be providing the SQL server name or instance name and then you have to provide the database name where you want to load the files so here we have test DB test the connection looks good fine and now we have to select the table where we want to load the data so we are reading from a uh sheet custom so we are going to load the data to the customer uh table so click on the customer uh right now it is mapped correctly everything looks good but if I hit okay I'm going to get some error those will be related to data conversion so in the Excel what is read it might it is providing a different data types what we have in the SQL Server table so let's go back to lb destination here and take a look so that's how I do open my destination then H over The Columns and I see here oh first name is DT wst or that's n worker coming from the Excel on the other side I hover my mouse and see okay in the destination I have dtst that's War 100 so that's how I uh you know over my mouse and take a look what are the uh data types on these columns and what are the difference between them and then I go back and fix them sometime you have mapping document where it is provided so if you have a mapping document you can take a look on that one as well and uh just convert to the required data types so click here I I brought data conversion I could have used uh dried column but when I use Expressions that's where I bring the dried column but when I'm doing straightforward data conversion I use um data conversion uh transformation so here I have first name and I want to convert this one to the dtst Str that's going to be our work and I know that it should be 100 as my destination um data length is 100 for f name this is going to be same and then we have a address 100 and this is a uh the see Excel took this phone number as the double Precision float but that's not uh this should be actually string as I have the numbers and hyphens in them so um I will be using string so I'm going to convert that one or even I don't have hyphen but I still want to keep this one as a string this one region code it to uh 255 and Walker but there are only two characters in region code so I converted to two here you have the output alas for these column names uh I recommend changing to this one to the some reasonable name instead of copy copy because in other transformation if you will change or create a new um columns they will be always look like copy of this column and copy of this column you want to do this one maybe data conversion underscore U your column name so that's how you can distinguish them okay this was done from the data conversion and if you have done from Drive column you will say maybe drv or something like that so that's how you can rename them correctly and hit okay now let's go back and map remap them so first name is now we will have DC first name so data converion first name for last name we have DC last name in the address I didn't make the change so I can use the copy of address uh uh column here I will be using copy of phone number and here I will be using copy of region code so any name I changed they were they became as a DC name and whatever I didn't do it they were come in as a copy of those columns so hit okay now there is a sign uh we see here this is called warning sign so this is coming excuse me this is telling us thata might get truncated because uh your destination is 100 worker 100 and what we are getting from the top that that might be you know n worker 200 and those kind of reasons so you can go back here this one is actually related to the phone number and it is saying okay what is happening let me fix it actually um phone number has a length 10 and here we have 50 so if you have 50 character coming in and this is phone number is 10 it might truncate if the value will come more than 10 characters so I can go back and fix it so that's not big deal we can just change it here and that's it now it is gone so select contr a or just with the mouse you can select all the transformation source and destinations and then go to format and auto layout and diagram that's how you can format your Transformations or sources and destinations and you can drag them in middle so this part is done let's go to the second part now in the same data flow we can load the other file or we can use the next data flow and load the uh customer short sheet so depends wherever you want to use it you can run them parallel or you can run in sequence let's do in sequence for now so here I'm going to rename this one load customer and here we will be loading customer sh all right double click on customer show data flow task and now we need to bring the Excel Source again here we brought the Excel source and then the next part is double click on Excel uh Source here we do not have to create a new connection because we are reading from the same file so we are all good uh here what we need to do we need to only select the different sheet name so we are selecting a customer short here hit on columns and then next part is bring the data conversion now because we know that okay we have worker in the destination and we have n worker coming from the Excel so let's convert them right now so we say first name last name and I'm going to call them DC Name DC last name and I will be convert converting this one to dtst dtst and I'm going to keep this one to 100 hit okay now the next part is the bring the olb destination and then make a connection to the SQL Server table here we will be using a the same connection will be used if you are loading the data for to the multiple databases then you might have to create a new database connection otherwise you are good with the same database uh connection what we have created so uh I'm I'm going to load in the same databases my TBL short name uh and customer sorry yeah short uh customer and customer short they are in the test DB so I don't have to create a new Connection Manager for uh customer your table so if you have uh tables in different databases you will be creating multiple connection managers so here we will be selecting customer short if you want to have some keep identity keep null and all those kind of thing you can select those but I'm going to keep this one as it is no changes so hit mapping here I will map to the DC first name and DC last name so we are all good here if we will run this package now what it will do it will read the data from the sheet what we have provided in the connection manager so in The Connection Manager we have provided um Excel customer file 2015 0306 and it will load this file to the two tables but what is the case here we have multiple files and we want to Loop through and use different files uh on each of the iteration so what we want to do our package is ready next part is we will be using for each Loop I'm going to bring for each Loop here and then I'm going to drag our data flow tasks in the for each Loop container okay now hit for each Loop container and next is collections what I can do here uh uh cancel this out let's create a variable that we can use for this folder path so why I want to create the variable because on different environment such as production or U we will have uh different paths from where these files will be coming and if I will use as a variable here I can pass different folder path according to the environment by using ssis configuration so I can call it input folder path this is going to be string now we are all good here let's go in the for each Loop container editor in the collections scroll a little down so we can see so here we have expression in front of expression hit it this B small button and then go to properties here we say directory in the directory we will be providing that variable from where our files are coming through so hit okay now the next part is do you want to read the all the path um uh you you do sorry do you want to read all the files static. estc that means you want to read every file let's say if I'm only interested to read the xlsx file so I can change that one so if there would be text file sitting there I don't care about that so I'm going to change this one to the X do X LSX so I'm interested to read only the Excel files next part is what you want to read you want to read name and extension only that will give you the file name and extension or fully qualified that means it will give you the folder path with file and with extension and I think so this is what this is what is going to work for us so we will be using fully qualified if you want to read only the file name you can use that one as well so I'm going to use fully qualified uh transfer sub folder if we have sub folders in this main folder and we want to read the data or files from them we can use this property but I don't think this is not applicable in this scenario so we are all good here let's go to mapping now and then we create a variable that will hold the full path hit okay so this is all good now the next part is we go to the Excel Connection Manager go to properties here we will be providing that path so we can provide Excel file path so that's where we will provide the full path variable this is come in with the folder as well as the file name so hit okay hit okay so you see that once I provided this one the data flow task came with the Red Cross because it was trying to validate them and there was no value I have in the full path variable so what we need to do here we need to ignore this part by use setting the value delay validation to true so let's set delay validation to the true hold on one second here let's go to the data flows first and set the value delay validation to true we do not want to validate at start of the package why because uh our file name is coming from the for each Loop to The Connection Manager that might not be available at the first so we do not want to validate these data flow tasks at the start point they will be validated when they will be executing so that by the time we will have the file names correctly coming from the for each Loop so we are all good here let's go to the Excel Connection Manager and uh try to find out if we have property here called delay validation here so we set this one true as well so we are all good now so let's save this one and now run our ssis package we do not have any data as of now in these two tables run this package so the first file is loaded second file is loaded all the sheets are loaded correctly so we see the success with this green check marks so let's go to the tables and take a look so if we run this one we see that all the sheets are loaded successfully let me run only the short uh customer short so it can show you the record count so I have record count here you see that that's 53 records if I go to the Excel files here you see in the first one I have only four uh records and in the second one I have 49 records so that's how they become 53 so the all all the files are read all the sheets are read and loaded correctly to the SQL Server tables excuse me now our package is done next part could be okay I want to Archive those files those you could have used the file system task here and archive those files I have have ssis package uh that I created in one of the video I'm going to post that link in the description as well how to Archive files with the daytime you can use that part here at the end of this um data flow task and archive files after loading them so thanks very much for watching this video and uh I hope uh uh this was not too long video this is early morning I'm taking my time and uh I also have some comments from other users they say you speak too fast and I want to make it slow so the people can follow stop at different points and do the uh task on their ssdt or bids and create the package with the video and run it thanks very much once one more time and I will see you next video

Channel: TechBrothersIT

Views: 95,968

Keywords: Microsoft Excel (Software), Multiple, Sheets, Excel Files, SQL Server Integration Services Interview Questions and Answers, SQL Server Interview, SQL Server 2014, REal time SSIS Interview Questions, Software, Technology, SSIS, Database Adminstration, MSBI, Business Intelligence, Business Intelligence Tools, ETL Tools, SQL Server development, SSIS 2014, Load Excel Files to SQL SErver, Excel, Table, SSIS Tutorial for beginners, Database (Software Genre), SSIS Real Time Tutorial

Id: 1WXKpkwjhX8

Length: 19min 47sec (1187 seconds)

Published: Mon Mar 09 2015