SSIS Tutorial Part 24-Load Files to Different Tables according to the File Name in SSIS Package

welcome to tech brother with the AMA today we are going to learn how to load files according to the names to the sequel server table so what we have here we have different files with the names and one of the file is called customer file and then other one are also the customer file underscore date/time the next one is employee information file and they are sitting in the same folder what our goal is a load these files according to their name so we have two names here customer file and employee information and we want to load them to the two tables customer table and employ table okay so if you guys have more file like let's say you have hundreds of Al and you have hundreds of table I mean this approach is not going to work for you but if you have a let's say five or ten or 15 and you want to create your SSIS package to handle that this is a way to go but if you have you know large number of files and then you might want to come back and check one of my posts on sequel a dot blog spot.com to how to do it by using the script tasks ok so but I will go ahead and you know create another video for that part as well so let's start with this one for now so I have two tables according to the names of the file so that's what I want to load let's go and create a SSIS package so I have already opened my project so right click on desk is package rename the package and we can call it load files according to the names alright you can give any any good name but this is just came in my right now so I'm going to give this one so the very first thing is we are we are going to read the files from the folder so to read the files from the folder you know we we have to have use for each loop container we don't know how many files are going to be there tomorrow maybe there will be 20 file with the customer table customer file name and there would be another 5 or 10 for the employee so we have to loop to each of them and load them so let's loop to this this folder so to loop through we have to have a folder path we can hard code inside the SSIS package but that I do not recommend so I'm going to use a variable I'm going to create a new variable and call it folder path ok this is going to be string type all right ok so the next part is a select that enumerator so we have file for each of file enumerator so the next part is instead of providing the path here folder path here we need to go to expressions and go for directory and put the expressions and the folder path variables will be used to get the value ok so if we go back to our folder we have only dot txt file let's say tomorrow somebody put CSV or Excel or any file we do not want to read them we want to read only dot txt files ok and let's go back and just for this package change this one to dot txt so read all the files that has extension txt and read the file name and extension ok that's what we are going to read ok so we are going to save the file name with extension in this variable so we call it file name ok now we are reading the files you know on each of the attrition and what we need to do we need to load those files okay to load those files we know that we have to use a data flow task okay so what we are going to do we are going to bring up data flow test here okay and as we are going to load two files so I'm going to bring two data flow tasks okay so I'm going to name this one customer DFT customer and I'm going to name this one DFT employer okay so when we the for each loop is going to loop through it is going to read the file but how we are supposed to know this data flow test should run or this data flow test Udrih that's where we are going to use some expressions and president's constraints and the move the moved or redirect the flow to the correct data flow task guarding to the name of the file so I'm going to bring up another task here and that will be called script task okay so what I'm going to do I'm going to connect this one here and connect this one here so what I want to do here when for each loop will read the file name I just want to read the file name and then write some expressions here you know and if those are less a equal to the customer file I want to move to this one to customer side if for the file name is read is the employee information then I want to move the flow on this side alright so if we go back and read up they take the file name the complete file name for employee or the customer name so what it has it has the date time and dot txt added to it okay so what we can do we have two room move the apart date end time and just read a customer file you know and or employee information part of that and then compare it but what is happening here okay what is happening the customer file that has a mmm like 12 characters and this one employee one employer information has more characters we cannot really use a substring so what I have done I have written a sequel statement to find out what I need to do okay so what I did here I took this information okay and then I reversed it once I reversed it and then after reversing it I took let me let me work on this one and show you real quick like how I did it so I and I also recommend you to go to this one you know by when you writing expressions and all that you might want to you know use a sequel and write it here and test it and then go and write your expressions okay so I took this one I reversed it so now what I can do I can use substring to remove the first correct that has dot txt and date temp time to it so I can start at 25th so I can count them and then I can start it you know after the time part or dead part and that's 25th and then I want to go and read all the way length of this string okay so I have provided this path here I don't want to just say read five practiced and practiced because filename tomorrow okay this can be long long you know and especially for the customer and employer they are different information so I want to take the length whatever is remaining so if I run this one now it left with the file name only so what I want to do if I would reverse it again it is going to bring me only the file name and the date and time part is gone so what is happening here I'm using the substring reverse functions to get n length functions to get the file in part of the from the file you can use any other functions you know different people have different ways to do the things so it does not doesn't really matter as long as it's worked for you okay so what we need to do now here we need to go on expressions presents constrained redditor sorry we have to click on green line between the script task and a data flow task the script has the role of the script as in right here is nothing but just to have you know control the flow okay so I'm going to double click here and then expressions and constraints so I'm going to write a expression then I'm going to constraint like when it is successful so it is going to be successful always because script a say we haven't put anything so hit this tab and then it is going to bring you here so what we need to do we we need to use the same expressions you know what we have written so what I did I also prepared this expression that's the same thing you know reverse substring reverse but instead of putting the entire you know with the single quotes the filename it I put I have put it with double quotes so what I'm doing here so I'm reversing it this part and then I'm taking the fluting you know starting point from 25th character and going all the way the length of that string and then I'm comparing with the employ employee information okay right right now we are writing for customer file right so if I will writer and we do not need to provide this values here it was just for a specific purpose so what I'm going to do here I'm going to use the file name okay so that's pretty much it and it's it's going to be evaluated correctly file name and now we are comparing with the customer okay yeah customer for all right if I will evaluate it is then it is a incorrect so let's take this one and we run one time right yeah we can see what is happening here right now there is no file name in this variable saved so that's why it's again same false all right that's fine so we can live with it and if we want to test it more what we can do hit OK take the file name from one of the folder what you have here and then just provide in the variable so we can test this value you know that's good idea actually so you click on variables go to file name provide the file name here so now if we go here and then try to evaluate them it's coming true so if the file name will be coming with the date time and you know this is going to be true so let's the test for the second part we can use the same expressions but just we have to compare with employee information file name ok let's go here and double click here sorry and then constraint and expressions and click here and put it here ok and then we are comparing with employee information so I'm going to copy that one if you are expecting your file names will be come in in the upper and smaller cases I recommend you to make a pair on both sides you know and that will be comparison will be always correct ok so evaluate it right now it's coming false because the file name is customer in the variable right now if we want to go back and change it and then to retest our expressions we can come back so I'm going to take this file name ok back here and now change the value of the variable to test this part okay so what we see expression is evaluated successfully and if I will go here and it ascends it is a true because now it is reading the file name from a variable and that part is equal to the employee information okay so what is going to happen right now if I run the package it's not going to load any file or anything it's going to just on each of the duration it is going to move to dead customer data flow or the employment employee data flow let's run it just for testing purpose and then we will build it okay so everything looks good and next part is we need to bring the sources and destinations and put inside the data flow task let's go in the customer data flow task good sources and we go to the flat file source here okay and make a connection to the customer file you can select any of them right now because these will be the connection string will be overwritten with the file name one of you each of the iteration of for each loop so it is common to limited yes it has the first column header row in the first row that's fine so if you want to change the data types you know I recommend if you can do it here so you don't have to use the data conversion Overdrive column transformation to change it so maybe I can leave this one you know the video is getting long so I'm going to leave this one as it is alright so go to preview hit OK now retain null values if you are getting the blank values and you want to convert them to the null you can select that one hit OK alright so we are loading this the information to the destination the table called the customer table so we can use old lady B destination ok double click here go create a new connection provide this equal server instance name and then provide the database name where your table is test all right okay fine so here we are loading the data to the customer table map the columns so first name first name last name will be going to the last name and that's it all right so we have configured the customer or we have a you know at least done the configuration for the customer table but we haven't changed the connection string yet that's fine so let's go to the employment employee table right now and then do the same thing read the data from the source by using flat file source now it took the connection manager automatically I recommend go back and change this one you might want to change the name of this one to the connection underscore customer you know so you would not confuse yourself and here what we need to do we need to bring a new one so we are going to let's change this one here we can call it a connection it's good employee all right and browse to the new file employee information that's what we want to load right it has the column names in the first floor that's wine and leave the you know rest of that as it is go to columns go to advanced change a data type so if you want preview it and that's it okay so go to columns just take a look the columns I have a lot of videos on how to load the files you know in different from different formats tab delimited and pipe and whatnot so you can see that and if you have problem here I'm going to move fast you know so let's load the data 2d sequel server table by using old lady B destination here ok so now let's create a nuke we have the connection already actually because our both of the tables are existing the same database so we don't need to anything and we just need to select the employable okay so we wanna keep done t-table our check and stay and everything leave this one as it is doesn't really matter at the moment so we have first name and we have a employee last name that's what we are mapping from source to destination all right okay right now we see the warning it really doesn't matter because the phone number is read as a string 50 and in the destination is it as a string 10 I told you guys if you want to change it you can you know in the flat file source and the word is warnings so to do this real quick if you want to go back ever and say okay when I change it and you know remove these warnings you can always go and go to advance and phone number and here it is 50 so make it 10 so if you do this one what you need to do you have to come back and refresh your source and that that's where it's going to you know take the changes alright okay so we have made the connections to the files but those files are you know the static files and they are not changing with the filename what we are reading by using for each loop so now next step is we go to the connection managers or for these flat files and make change so we go to go to the connection manager of customer properties go to expressions and now we need to get the connection string property and replace that one with the folder path plus we have to pass the file name that will make it the complete connection string right now is we have put the value here that's why it's yone but this will be overwritten you know when our exercises package will run all right okay so that's it we do the same thing to the employee go here and now what we need to do here go to the connection string and the and write the same expression I could have copied and pasted here there is no change in that expressions so and now need to provide the file name here okay so once these connection managers are going to use our variable we are good to go so let's run the SSIS package and see if the data will be loaded to our tables so we have table customer let's select some data and truncate this table just in case this was there from last demo okay table concrete table I'm going to copy the other one as well I'm truncating the both tables and making sure there is no data okay so it's like so if we run this select statements right now there shouldn't be any data in these tables here okay all right so let's run SSIS package and see if the data is loaded to these tables okay the package is looping through the files and then redirecting them according to the part of the file name so now we go back and check our data all right so this one has loaded three files in the customer table and the data from the employee should be in this table so we see that the data is there okay so what's my recommendation if you have a let's say five ten different type of file you know this could be best solution you can do it and run it it is going to loop through and you know redirect according to the file name and load it but if you have hundreds of them it's not a great idea and it will take you forever to write the SSIS back and write expressions evaluate them and everything so you might want to use the scripting you know in those cases thanks very much for watching this video and that's you guys next time

Channel: TechBrothersIT

Views: 64,475

Keywords: SSIS Package, SQL Server Integration Services Interview Questions and Answers, SQL Server 2014, SSIS, Database, MSBI, Business Intelligence, Business Intelligence Tools, ETL Tools, SQL Server development, SSIS 2014, SSIS Tutorial for beginners, Real Time SSIS Scenarios and Solution, Load Files to Different Tables according to the File Name in SSIS Package, SSIS Best Tutorial for Realtime, SQL Server Integration Services(SSIS) Tutorial for Beginners, Tech Tutorials, TechBrothersIT

Id: QIMziWqmADE

Length: 21min 46sec (1306 seconds)

Published: Tue Dec 23 2014