05 Load only new files in SSIS 

Thank you so much.

Thank you Magaba for your comment.

Glad you found them useful.

You are most welcome Sandra.

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

Glad to know that you liked this video.

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 😀

You are most welcome Mahi.

You can schedule the SSIS package using SQL Agent job ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qrV2FbQpU3I.html 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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wxjhlNQmje4.html You can also use a C# code in SSIS to remove the special characters ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wTVoSX37ols.html

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

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.

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.

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-DEx7mDhSkp4.html

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.

@@learnssis makes sense. Thank you brother.

@@huzischannel you are most welcome.

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.

@@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

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

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8dRIjVhKoKs.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-c2lSKLQPb_I.html

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.

@@learnssis thanks mate, now it makes sense!

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.

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8dRIjVhKoKs.html

Hi Naveen, Take a look at this video, I have shown the same thing in this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-O1xVlKq8cqY.html

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.

@@learnssis oops got it sir!! thanks.

@@HarshaV44 no problem.

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

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

@@learnssis what should do next

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

Both options can be used.

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.

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

@@learnssis you are awesome! Thank you.

@@geeseeoh No problem, below is the link to the video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Hqg3Cfccuz0.html

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

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.

You can check this video on delayvalidation ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oeLgFxQ9vT0.html

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.

@@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..

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

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

@@prasadchowdary5954Can you take a look at this video for understanding the foreach loop container better ? ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BjpaSxMZMxs.html

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.

@@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

@@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.

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

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

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

@@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

​@@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.

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.

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

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.

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

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

In this video, I have shown how to find unique most recent records from the table. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cGkhIGqIcJI.html

@@learnssis no, there is no date column