SSIS Tutorial Part 28-Archive Files with Datetime after Loading into SQL Server Table

welcome to tech whether with the army today we are going to learn how to load multiple platforms into sequel server table and after loading them or hide them by adding date time to end them and let's see some flat files what we have so we have three files here customer file underscore a SEO customer file underscore EU Europe and customer file underscore n in nano txt so we have these files so what is our requirement every time we get these files on daily basis you know at nightly processor and then we need to load these falls into sequel table ok that I already have created with ID first name last name and phone number and address so we need to load the data into this table and after loading we want to archive them by adding a underscore date/time to them and move to the folder I have already created one folder called archive folder ok so let's find out archive folder here ok so right now there isn't any file in this archive folder because we haven't loaded any file and we haven't moved any file so let's create SSIS package and then do the step by step of how we can achieve this so the very first step is create new SSIS package and this one we are going to save load flat files files and archive them archive ok you can give a reasonable name in my case is just random name I am given right now ok so the very first step is what we need to do we need to read the flat flat files or text file from our input folder let's create a variable where we can say about this folder structure or folder path so we can call it input folder ok and then this is going to be spring ok let's create another one and call it archive our typo okay so these variables are going to hold the value for the folders okay let's go back here input folder copy this path come back and provide the value okay and now let's take the path for archive folder and provide in the SSIS packet to the archive folder variable okay so we have two variables now what is our next step first of all we want to read these files and load them to the sequel server table so what we can use we can use for each loop container to read these files and load into sequel server table and then we are going to archive them after load so the very first step go to collections and we are using file for each file enumerator so we are not using this folder path here because I don't want to hard-code anything I want to use the variable so we can change the values of those variables by using configuration in different environments such as production UAT or si T while we will move the packages to different environments okay so the directory is our folder okay input folder so evaluate that and then hit OK so in the expression we have provided the folder path to the directory property the next part is what exactly we want to read it to in my case the files are going to be text files so I'm going to leave a strict dot s trick that's fine and I'm going to read only the files those those have extension txt okay and what I will read I will read the name and extension of that file ok so we are not reading from the subfolders and we are only want to read from the main folder so I'm not hitting this checks box okay so let's go here now the filename and extension has to be saved somewhere so we are creating a new variable called it file name ok and this is going to be string as well this is a variable that's going to hold the value on each of the attrition for our filename and extension hit ok the next step is the putting these files up to the sequel server table okay let's make it a little bit so we can have some space to load the files into sequel server table we need to use data flow task inside the data flow task what we need we need a source so that can read from the flat file that is going to happen flat file source okay so let's make a connection make a new connection and browse to the file right now we can select any file the actual goal is reading the metadata information for this file and as these all key files have the same information or definition and that's that will be you know that's fine and that will be overwritten each time our for each loop is going to provide us the new file name with each iteration ok so you can point to any fun for now and then hit OK do provide text qualify if you have it you know if a header row delimited a spine skipping row if you are so you can provide it here column names in the first data row okay I have the column name in the first row that's fine and then going to the next columns and I can see the data is coming correct I can go to advanced and I can change the data type of the columns or the names if I needed okay in this case the ID is integer so I have chain to design integer first name last name I'm going to leave this one as it is for for my phone number I'm going to change this one to 10 as I have created the table with this definition okay so if you go to my customer table you will see that I have identity first name last name and to address 100 we're in a flat file source is red at 50 some point with that and phone number is 10 ok let's go here and retain null values if I am getting any null values I want to convert if I'm getting any blank values I want to convert them to the null so I click that one columns I see the columns everything come in correct okay so next step is loading the data to the to the destination so we are loading the to the sequel server table I can use old lady B destination and then make a connection to the table okay double click and then go to new and as the connection is already available I can use this one or I can create a new one so you have to provide the sequel server instance name and then you have to provide the database name okay so this is my server name and the database name is test DB test the connection everything looks great alright so now the next part is select a table in which the data is going to be loaded it is customer fine and then we have the mapping so if the input column is M is it have have the same name like a destination column it will match automatically and map it if not you have to do some manual work in our case first name and last name is not the exact in the source and destination so we have to map them manually okay hit OK so what's going to happen now our for each loop is going to read the fall you know loop through the files one one at a time and bring us to the data flow task but inside the data flow test if you see our source or connection manager is pointing to the one far ok what we want to do we want to key there one with the with the filename variable so each time it should get the new file link so let's click here go to the properties of fire flat file connection manager and then we have to find expressions ok inside the property what we are looking for we are looking for connection string connection string is a complete path of a file that includes folder structure or folder path and file name with extension so we have to provide that one ok so what we have here we have input folder from where it is going to be read and then we have to provide the file name so I'm going to add the file name ok right now there is no file name and the value in the file name variable is nothing that's why we didn't see anything here but it correctly fine hit okay now this the SSIS package is ready to load the data from flat files into the sequel server table okay let's go back here come truncate our table you know and select some data we don't see anything let's run the SSIS package and see if it is loading correctly okay so the package completed successfully good so let's go back in SSMS and run the sequel query and see the data yes so the files are loaded successfully that's good news for us okay so let's run gate this table one more time now we know that we are able to read the files and load them successfully in sequel table after loading we want to move them to the archive folder okay to move the files to the archive folder what we need we need a file system task this task is going to help us to move the file so we are going to connect the data flow test to file system tasks so each time file is loaded we want to move that file okay let's double click and see what are the requirements so the very first thing what we see here it is a scan source connection okay is that your file is coming from some variable yes we know that it is you know we have the file name variable and its then come is a complete path but right now what we have we have a separate path for our full folder path and we have file name so we need to make that connection and put it here so how we can do it we have to create a new variable called input for full path okay so what is this this is going to have input folder plus the file name so that's what we want to move okay so click here on the expressions if you don't have SSD T you have can press f4 that will take your properties and then you can go to expression and write it okay so we have a input folder plus all concatenation we want to concatenate the filename so this is a complete path of a file that we want to move okay so one thing we have it that's correct so what we can go back here and say is the source path variable that's true source variable which is holding complete path of our file okay so that's input full path fine now we do not want to copy the file what we want to do we want to do rename file okay what we are doing we want to rename and move the file at the same time so we have to select rename file good okay what is next is destination path where you book yes we want to have that okay and if the file does exist you want override it yes we do want to override it and destination variable what is my destination variable is archive folder plus the file name and I want to add the date/time to it I don't have that complete path in any of the variable right now so what I have to do I have to prepare that so let's hit OK and come back and create a new variable and call it full archive path okay so this is also going to be string type all right let's go to the expressions what we want we want to have a archive folder okay and then what we want we want to add let me see a mechanics mistake we have to use addition sign here okay so I have put this one the next part is file name okay so I can get the file name from here fine but if you guys know that I have to put this one here okay right now I don't see that anything here all right so what I want to do I want to come back to this one and for now I want to provide a sample file name like let's say test file because I want to evaluate my expressions to make sure it's working correctly okay so I'm going to the full archive folder one one more time you know now if I will see I can see that how it is evaluated okay so you can always provide the filename and value to that variable just to evaluate it because it will over return the value test or text dot TTS he is not going to be used okay you can delete it later or you can leave this one as it is when you run the SSIS package it will be overheated okay so you have this all Chi one I do not want to have dot txt at then because I have to add date/time to it okay so I want to replace this value replace comma what I want to replace dot txt okay and then with what just blank okay double quote double quotes okay now I have filename left only what is my next step I want to add the date/time to it so I can use the get date function but problem is get date when I try to evaluate it it's saying I cannot concatenate this value to these you're already expressions because they are not compatible okay so I am going to convert that this date time to the spring duty underscore STR and I'm going to have fifty value comma 1252 that's a collation code okay so I am able to concatenate the values now but I should actually put underscore between the date and filename okay so what I'm going to do I'm going to add double quotes here underscore double quotes plus sign okay let's try again okay so we are able to put it the very next step is that I want to get only the date and time from it I don't want a milliseconds and then a seconds all the way so I can remove that one so I can use substring okay and I can get the only parts of 1 comma 19 that's going to give me the first part that date and time and second till second so okay next one I want to remove this - xur you know from the date so I can use the replace function again okay and I'm going to replace the hyphens with no space double quotes double quotes okay and next part I have sorry here it should be - guy I remove the colons I'm going to remove the colons as well though okay so I have removed the hyphens from the dead part now I want to remove or replace actually replace the colons up with the no space so I'm going to put comma double quotes colon double quotes comma double quotes double quotes without no space okay so now I have a date and time added to it okay is my choice I can remove this one and this space I can also put underscore if I want it so depending upon your requirement then at the end what you want to add or you want to add the extension back okay so now your file will be something like this file name plus date plus time in hours minutes and seconds and you have extension attack okay so we have a created of this variable and then a road expression on that that's going to have whole the complete connection string for our archive file okay now let's go back to file system tasks and then what we need to do here we need to select that variable full arc iPad okay now let's run the SSIS package and see if the files are loaded successfully and move to the archive folder okay so SSMS no record is there good so we are and we go to the archive folder no files are there so fine let's run our test okay so the files are loaded successfully and the moved correctly to the archive folder so the very first thing let's check the table if the files are loaded now okay so the files are correct correctly loaded in the sequel server table that's good thing next they are moved to the archive folder you can see the archive folder here and there the file names are you know appended over the part date/time is added to them and the day amount let's go to the input folder and see if they are there Wow so they are not there okay so that's how you can create a package that will read the data from different files and load them to the sequel server table or Oracle or any other destination and then you can archive them by using flat file source thanks very much for watching this video and see you guys next time

Channel: TechBrothersIT

Views: 72,074

Keywords: SSIS Package, SQL Server Integration Services Interview Questions and Answers, SSIS, Database, MSBI, Business Intelligence, Business Intelligence Tools, ETL Tools, SQL Server development, SSIS 2014, SSIS Tutorial for beginners, Tech Brothers, Real Time SSIS Scenarios and Solution, Archive Files with Datetime after Loading into SQL Server Table in SSIS Package, Archive Files in SSIS Package, TechBrothersit, SQL Server Integration Services(SSIS) Tutorial

Id: M6pzD7WHbBw

Length: 18min 32sec (1112 seconds)

Published: Wed Dec 24 2014