147 How to store File Path into sql server table using ssis

hi friends my name is Akil Ahmed and in this particular video tutorial I will show you how to store file path into SQL Server table using ssis so the agenda of today's video tutorial is how we can store the file path into SQL Server table so recently I got a question from one of my subscribers and here are the similar question so I thought to make a video on this one so let's jump to the demo this is my D files location and in this particular location I got two files emails dot CSV and emails underscore current airtime.csv so what I want to do I want to input all the files from this particular location into a table email table into SQL Server instance so I got this SQL Server instance and I got it test database so I want to import all the data from the CSU files into this particular database in a new table okay and there should be an additional column created which we can call maybe file path and that file path should contain the actual path of the file that is being loaded so that you can easily identify like which data is imported from which file okay so maybe if you want to see the file like what kind of data it contains so I can open this particular file and it contains person's ID first name last name email and gender okay so the first file it contains 1000 records and the second file it contains only 5 100 records okay so there should be total 1500 records so let me open this exercise package so this is my blank ssis package that I will be using today and the first of all what I will do I will create a file path ssis variable here so I can call it as file path the new exercise variable and the data type will be listing of course because I want to store the path of the file so we can give an initial value to this one maybe I can give as email the files emails dot CSC and I can provide the default value here okay now I can close this one now the first thing that I will do is that we will use a 4ish loop container because you want to Loop through all the files in a folder so that's why you will be using a 4D slip container now we can configure the first Loop container here because you want to look through the files so you will be using the footage look file enumerator okay and now we need to provide the location of the size like in which location the files are situated so my files are situated inside D files location so I will provide this location here now we need to provide like what type of file you want to import so I want to import the CSV file so that's why I given star.csv here and it will import only the CSV files from this particular folder now I can go to the variable mapping and from here I will select the SSRS variable file one that I created so now what it will do whatever file it will read it will just get the file path and it will assign the file path to the file path ssis variable okay so I can click on OK so we have configured the fully slope container now it's time to configure the data flow task because data flow task will be used to import the data so I can just open the data flow task and then I can configure it because in the data flow task we will read the data from the flat file so we will use a flat file Source here now we can configure the flat file Source click new to create a new flat file Connection Manager and then we can browse the file so the file type is CSU so we'll select CSV here we can select any of the file from here if you click on preview so the data seems good here so I can click on OK okay now because you want to store the data into a SQL Server table so that's why you will we need to use the oledb destination here okay so we can just drag and drop the oledb destination here but before configuring the oledb destination we also need to use the drive column transformation because in the derived column transformation we will add a new column file path into the data flow okay so let's connect the flat file source to the drive column transformation and now let's configure the right column transformation here we can just click on the plus sign here and we can just drag it up the file path into acceleration so a new column will be created here okay and we can call this new column as file path okay but if you look at the data type so it's the data type by default it's taking as Unicode string you know anywhere here and the length is only 19 so maybe we need to convert it to you know where care because all the data in the CSV it's of where care type so that's why it will be better if you convert the data type to worker and maybe Lan 3 should increase to maybe 500 or something okay because the file name can be landed so to Typecast it what we we can do there is a function here if you click on the type car so there is a function to type cos it to a stream so you can just drag and drop DT underscore HDR function here and the length he can give maybe 500 and the code page is 1252 for the uh worker so I can give code page 12 feature to here now you can see that it got converted to the vehicle 500 okay so that's the main thing that we need to do now we can click on ok now I can just connect the drive column with the OLED with destination right click only to be destination and configure this one click new to create a new oledb Connection Manager there is already one connection to the test database so I can select this connection from data access mode you will select table it will Fast load we can click new to create a new SQL Server table okay so if you see here in the test database right now we don't have any email table okay so I want to create a new email table here so email Table Right Now does not exist okay I can copy the email name from here the table name from here now I can call this table as email okay and it has taken like ID first name last name email gender from the flat file metadata and because we added one additional column file path as well into the data flow so that's why it has taken its name as well so I can click OK and it will create an email table so the table got created now if you re-execute this query so you can see a table here but the table is empty as of now I can go back to the ssis package and I can click on mappings so you can see that all the input columns have been mapped with the destination columns automatically so that seems good now I can click on OK now the thing is the flat file Connection Manager it is hard coded as of now because it will only load the data from the email table as of now so what we can do we can right click on it go to the properties and we can go to expressions and from the property you will select the connection string property and we can click on these three dots and we can assign the value to the connection Instinct property from the file path SSS variable click evaluate expression we assign the value from the file path because the value of the file path will change for every iteration of the for each Loop so that's why it can import the another file as well so this is done now you can click on ok ok now our ssis package is done what it will do now the Forest Loop container it will Loop through all the files from this particular folder and will get the file path and will assign the file path to the ssis variable file path okay if you see here this is the file path variable now wonderful each Loop container will run it will run all the tasks inside the for each Loop which is the data flow task okay and in the data flow task it will get the data from the Flats while using the flat file Connection Manager and because the connection manager is dynamic we are getting the path from the file path variable so that's why this value can change for each file okay and in the drive column transformation we are getting the name of the file path from the variable so this value can also change and then using the only TV destination we are writing the data into the SQL Server table so that is the overall package so let me click on start button and let me show you like how the package will work so the package ran fine first time it imported 1000 records and second time it imported 500 records from the another file so let me go back to the ssms and let me execute the query so you can see that so you can see that we got an additional column file path here and the value is D files emails dot CSV okay and if you scroll more so after 1000 records you know the name of the file should change so after 1000 records we got the another five two thousand twenty three zero six eleven this is the second file you know this file that we loaded and they should be only 500 records from this particular file okay so because the file number is started from the 501 okay so that's why there are just 500 records in this particular file and the total number of records are 1500 okay so this is one of the method like how we can import the file path along with the data into the SQL Server table now so I will share both the files with you and you can test the same process at your machine using the ssis package and if you're any question then you can comment on the video yeah 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: 1,898

Keywords: How to store File Path into sql server table using ssis, How to store file names in SQL Server Table by using SSIS, How to load extracted file name into SQL Server table in SSIS?, How to import data from text file to SQL Server using SSIS?, how, How, to, store, File, Path, into, sql server, sql, server, table, using, ssis, SSIS, load, extracted, import, data, file name, sql server integration services, msbi, learn ssis

Id: C8y6AVy2oEE

Length: 9min 54sec (594 seconds)

Published: Tue Jun 13 2023