05 Load only new files in SSIS

[@bl8896]

These tutorials are great, my only advice would be to slow down your movements on-screen just the slightest bit - it was difficult to keep up pace while learning. Some parts are slower and very easy to follow but other parts speed up - most likely to your natural speed when you develop as an expert. Thx

[@learnssis]

Thank you so much for your suggestion, will try to implement it.

[@amitkdhal9961]

This is good stuff ..one scenario could you please share how to identify new file from a folder and the old file we need to remove from the folder

[@nithishdpsnithu7623]

Thank you for explaining in Hindi I have understand very easily please make more videos in Hindi ❤

[@learnssis]

Sure Nitish will make more videos in Hindi, I also feel very comfortable and connected while explaining things in Hindi as it comes from your heart 😀

[@rathodindrajeet]

When I run this package data not inserting but log creating why and in my CSV file data starts from row no. 8

[@learnssis]

Sorry can't say what might be the reason without looking at the package.

[@rathodindrajeet]

@@learnssis what should do next

[@learnssis]

@@rathodindrajeet You should try to debug the whole SSIS package to see what you are missing or what went wrong ?

[@nekoskype]

Why not truncate or delete the values in the testData table instead of dropping it?

[@learnssis]

Yeah we can delete or truncate as well, If I am loading the data to an staging table then sometimes I can truncate it or sometimes I drop it as well.

[@huzischannel]

For execute SQL task....Any particular reason why you wrote SQL query in expression? Any advantages? I believe we can write it in a normal query with Question Mark(?) to replace it with parameters.

[@learnssis]

I feel more comfortable to write it in expression, because if there are 5 or 10 ssis variables used in the query, then if you copy and paste it in a text editor like notepad or notepad++ then you can read the code and can understand it without looking at the execute SQL task, if you are using ? Then you would need to check and count the ? To make sure which ? Belongs to which ssis variable.

[@huzischannel]

@@learnssis makes sense. Thank you brother.

[@learnssis]

@@huzischannel you are most welcome.

[@NebroProg]

Hi Ahmed Thank you very much for this great tutorial, I'm using SSIS for visual studio 2022, in the foreach loop, there is no option to select folder path or file extension.

[@learnssis]

You are most welcome. From the enumerator type you would need to select Foreach File enumerator.

[@mr.chandhu2025]

Hi Aqil, great video and thank you for placing this in KZbin. I need to insert atleast 5 files data on weekly basis to destination table without duplicates ,skip duplicate records and update only new records. How this process can be done in your package.can you please help me.

[@learnssis]

If you want to insert only unique records to destination table without duplicate records, then you would need to use the lookup transformation to compare your destination table data with source data and only insert the new records to your destination table. kzbin.info/www/bejne/bpW1ep2MnbCigdU

[@SandeepYadav-vm5hd]

for me Test data table all records gets deleted after I ran again to check whether it will again load same txt file. Could you please let me know why its happening in my case?

[@learnssis]

In Execute sql task we are dropping and recreating the testdata table every time the package will execute, if you don't want the data to be deleted when you execute the SSIS package, then you can remove the code to drop and create the testdata table.

[@naveenkumark6093]

Hi aqil, I have one doubt, how to latest files to table I mean, in a folder 5 files are there, these are same files but date is different, i have to load latest file, how ?? Can you suggest me anyvidea please

[@learnssis]

Hi Naveen, Take a look at this video, I have shown the same thing in this video kzbin.info/www/bejne/hWLbh5-Bpp2Wp7s

[@omelturk4609]

Another Great Job Aqil ! Thank You!

[@learnssis]

Thank you so much.

[@HarshaV44]

Hello!! while re-running the SSIS package the data in the test data table is erasing the previous data could you please help me out where I'm doing wrong?

[@learnssis]

Hi Harsha vardhan, in the first step we are dropping and recreating the TestData table, if you don't want the data to be erased while rerunning the table then you can remove the code to drop and recreate the table. Thanks.

[@HarshaV44]

@@learnssis oops got it sir!! thanks.

[@learnssis]

@@HarshaV44 no problem.

[@sriniwaaskorampalli8145]

sir why did u used "Into" fro inserting into logginh table? we can give "?" and paramaeter mapping............

[@learnssis]

Both options can be used.

[@fatimaez-zahraeouirini]

what if data in a database not in excel file and I want too transformet to another database like datawarehouse ???

[@learnssis]

Hi Fatima, if you want to transform data from one database to another one and want to transform only new\updated records, then you can take a look at this video in which I have shown how to use Lookup transformation to get the new\updated records and insert\update data to destination table. kzbin.info/www/bejne/bpW1ep2MnbCigdU kzbin.info/www/bejne/mWPPhH6ChrWVlas

[@mateusleao7093]

Another question if you don't mind: I was able to implement that in a production environment at my work: Why do you use this staging table, and why you pass data to another table (master)? Wouldn't it be easier to use the master table only, without using the staging table? I implemented what you did, and it works smoothly, the only thing is that now I'll have to pass the data to another table, because in the first drop, when I load new files, it will drop the first load I did. Isn't it better if I just take out the first drop? And accumulate the data only when a new file comes, without dropping it? Hope you understand my thoughts, I'm starting with SSIS. Thank you very much!

[@learnssis]

The concept of loading the data to the staging table is because if there are some records those need to be updated and some of the records needs to be inserted then it will be better to store the new data to staging table and then based on a join to master table we can update the data to master table. Even though if we only need to insert the data, then its better to load the data first to staging table because in case there is some data related issue in csv file and the load process stuck then you have the option to rerun the package and it will load all data to staging table again. If you are inserting the data directly to master table and the process stuck in between then it will be hard to analyse the inserted data and resume the process.

[@Karenshow]

can you do the same without dropping the table each time. When you have a 5M record table is not practical to drop and load every time.

[@learnssis]

Just remove the code to drop and recreate the table. Rest of the code will be unchanged.

[@mateusleao7093]

Why do you drop the table on the first SQL Query if the point is exactly only load new files? So if you drop the table when you run the query in SSIS isn't it recreating everything and loading all the files again? That's my only point of confusion...

[@learnssis]

TestData table is not the master table, you can think of this table as staging table, so every time the package will run, this table will be recreated, so you can think of this table as staging table and then update your master table from this staging table.

[@mateusleao7093]

@@learnssis thanks mate, now it makes sense!

[@solomong.gebrhana1204]

Thank you so much!! but How can we automate the process so it can run daily? And If we have unnecessary characters in the txt file(with out find and replace) how can we remove them so we don't have to see them in the table?

[@learnssis]

You can schedule the SSIS package using SQL Agent job kzbin.info/www/bejne/p6O5Y3mYhtWIaas And you can use a sql query to remove the un necessary characters from data. You can use execute sql task in SSIS to execute a sql query kzbin.info/www/bejne/ranNmZ-EhtKdm5Y You can also use a C# code in SSIS to remove the special characters kzbin.info/www/bejne/rYW5oIaOaJyiotU

[@mounikareddy2391]

For creating the static for excel file I used instead of connection string used excelfilepath in property please let me know is that going to stop static file loads

[@learnssis]

You are correct that for excel files, you will need to use excelfilepath property instead of connection string for CSV files. It should work. You need to test it thoroughly from your end.

[@mounikareddy2391]

@@learnssis Thanks for your quick reply . It works fine thanks for your video it really helps a lot . I have another question regarding dynamically calling the columns . If I use your video it only works for static schema throughout all excel file if I have different schema in other file it is not working do you have any other video to solve this issue

[@learnssis]

@@mounikareddy2391 Sorry I don't have any video at the moment for dynamic schema.

[@sriniwaaskorampalli8145]

sir.. what id delayvalidation used for ?

[@learnssis]

Delay validation property is used to validate the objects used in the data flow task inside the sql server, for example if you are going to read or insert data to a sql server table and if table does not exists when you execute the SSIS package, now if delayvalidation property is set to False then the package won't run, it will tell you that tables does not exits, and if you will set the delayvalidation property to true then package will start running and if table don't be there in the database then the ssis package will fail at that task where it will try to read or write to sql server table.

[@learnssis]

You can check this video on delayvalidation kzbin.info/www/bejne/pZavmHmuhp6pipI

[@Miss_Gabz]

Thank you so much, this has helped me a lot ❤❤

[@learnssis]

Thank you Magaba for your comment.

[@mounikareddy2391]

Thanks for the video. I have a problem of loading new excel files I created based on your video

[@geeseeoh]

What if i have 10 files in the folder, but only want to load 2 specific files with dynamic names daily and ignore the other files? Like file1-user_mmddyyyy.csv, file2-address_mmddyyyy.csv.

[@learnssis]

First you need to declare 2 SSIS variables and then if you have the file names stored in a sql table, then using execute sql task, read the file names from sql table and assign it to ssis variables. Now you can use for each loop container with File enumerator and then before using the data flow task to load the file, you can use script task and in the script task you will check if the file name of the current file that will be loaded is matches with file names available in 2 ssis variables, if yes, then there should be a third ssis variable for example FileNameMatched and assign the value Yes to that variable, and then put a precedence constrains between script task and data flow task and put a condition under expression and constraints @FileNameMatched != "Yes" This way only if file won't match with the file name from 2 ssis variables only then the file will be loaded other wise file won't be loaded.

[@learnssis]

I am trying to make a video on this one, I will let you know once video is ready.

[@geeseeoh]

@@learnssis you are awesome! Thank you.

[@learnssis]

@@geeseeoh No problem, below is the link to the video kzbin.info/www/bejne/fqLKZHacmMiosJI

[@geeseeoh]

@@learnssis thank you. I will watch it later after work.

[@AnkitSaini-qe9mj]

If I have to load only updated data or records from staging table to master table then what should I do?

[@learnssis]

Do you know what are updated records ? You can select them using OLE DB Source and can insert to master table using OLE DB Destination. If you want to see if the records are already in the Destination table then may be you can use lookup transformation. May be you can watch below video kzbin.info/www/bejne/enbbaKB6nbieppY

[@prasadchowdary5954]

Hi bro I'm loading different files to sql server by using ssis and for each Loop container in ssis by specifying files path in flat connection manager but after that i changed my file names then not working getting error can you please help me how read files inside the folder without specifying the files path name?

[@learnssis]

Are all files of same format or different format? If they are of same format then it should work. Follow the steps in this video. If you got the error then paste the error message here.

[@prasadchowdary5954]

@@learnssis same format all are CSV files bro but first time run it's working because I specified files path but second time i changed only CSV file name like →data.csv to info.csv like this i just changed names then not executing bro please help on this ..how to read total folder dynamically without specifying atleast onefile name also..

[@learnssis]

@@prasadchowdary5954Are you making the flat file connection manager dynamic as shown in this video ?

[@prasadchowdary5954]

@@learnssis no bro and how can contact you bro for better understandings

[@learnssis]

@@prasadchowdary5954Can you take a look at this video for understanding the foreach loop container better ? kzbin.info/www/bejne/eJvTkoaugr-ArtU

[@SandraRaies]

Good job ;D Thank you for sharing

[@learnssis]

You are most welcome Sandra.

[@mahia2030]

Thank you for the learning

[@learnssis]

You are most welcome Mahi.

[@MsMohanj]

Hi sir thanks so much im the biggest fan for your SSIS vedios Can you please explain log table how to store values like 'u'

[@learnssis]

log table is just another type of sql table. It is created just to keep the history of files loaded. So initially the log table will be empty and first time when we will try to load a file before doing that we will check if a file has an entry in the log table, if entry is found it means that file was loaded earlier because after loading a file we insert a record to the log table about that file, like file path etc, so that if we try load the same file again, before doing that we will check the value of file path in the log table and if value is found it means that file was loaded earlier and thus we won't load that file again.

[@MsMohanj]

@@learnssis if table is existing is it drop or skip the activities? Dbo.table and what is this 'u' is it any flag you set If I view table I can understand

[@learnssis]

@@MsMohanjFor the log table we should not drop it otherwise we will loose all data about the files loaded. We can drop another table if you think that table should be cleaned for every execution of SSIS package. Before dropping a table we should check if table exists or not. 'U' in the code is for User created object.

[@MsMohanj]

@@learnssis thanks so much of the explanation I learn SSIS from your channel now I'm working on SSIS and ADF

[@learnssis]

@@MsMohanjYou are welcome. Good to hear that you are learning both SSIS and ADF.

[@kiranraju3225]

Nice video and worth full tutorials.

[@learnssis]

Glad you found them useful.

[@b.n.v.sunilkumar5787]

Please post a videos on SSRS also...

[@learnssis]

Yeah SSRS is also in my list. And I saw these days Power BI is in great demand as well. Thus planning to create a playlist on SSRS and Power BI as well.

[@Fyvics82]

Good job... Thanks for sharing

[@learnssis]

Glad to know that you liked this video.

[@Amit-q8l]

Great !!! Your video really amazing. I have one question,could you please help me to resolve. Actually want to develop a SSIS package for importing Customer where the data is provided from multiple flat file sources. The customer primary data like customer id, name from one file and address information from different file source. The customer address data also contain multiple address information for each customer. need to get the latest customer information and merge it with customer primary data and import it into destionation customer table. How to design such a logic and it must be recent customer infomation is imported ? Please explain by drawing a diagram.

[@learnssis]

First import all flat files into individual sql table and then write a sql query to fetch data from respected tables and insert the data to your final table.

[@Amit-q8l]

@@learnssis can you share the SQL query to fetch latest address of the person.

[@learnssis]

@@Amit-q8l Is there any date column based on which we will know what is the latest address of the Person ?

[@learnssis]

In this video, I have shown how to find unique most recent records from the table. kzbin.info/www/bejne/mXjOmXx9pq6WgKs

[@Amit-q8l]

@@learnssis no, there is no date column

[@dilipkumar-ck1nc]

Code is missing plz copy the code in notepad and update

[@learnssis]

Its a very old video, I tried to find the package for this one but unfortunately I did not find the package.

[@marks2539]

thanks for the amazing videos. If you could upload a video about how we can create a package that can upload files from a folder which will be emptied daily and added with new files. so lets say on day 1 we have only 1 file inside C:\Learn SSIS\ as C:\Learn SSIS\A.xlsx on day 2 A.xlsx will be not there but there could be different files with same structure as A.xlsx the name could be different. B.xlsx and C.xlsx on day 3 again the folder will be empty initially and new files will be added with same table structure. so I want the excel connection manager to upload the files automatically from a fixed folder location.

[@learnssis]

Thanks. You can use foreach loop container and pass the excelfilepath to excel connection manager.

[@marks2539]

@@learnssis thanks for your reply, but i dont want to keep changing the excel path on a daily basis whenever the new file comes into the folder. Instead the excel connection manager should auto pick the new file

[@learnssis]

​@@marks2539 Because FileName can be different each day, thus you would need to make the excelconnection manager dynamic so that new file name can be passed to the excelconnection manager otherwise it will always load the same file. Now there are multiple ways to get the full file name of excel file, and one of the easiest method is to use the Foreachloop container to get the excel file name along with path, thus I would suggest to use foreach loop container for this.