22 Foreach Loop Container in SSIS Example

[@VoiconPisu]

Thank you for your sharing about how to use the Foreach Loop via Example.

[@learnssis]

You are most welcome.

[@sowmyareddy7503]

It's really nice explaining for beginners...Thank You

[@learnssis]

Thank you Sowmya.

[@MVinodKumar-tt1tu]

Hello Sir, We created variable Filepath and mapped it with foreach Loop and in Flat File Source we took initial file and in Flat file connection manager properties expression just mentioned Filepath variable. How it worked here sir? like how it looped through all files?Please clarify sir. confused

[@learnssis]

When you mapped the FilePath inside foreach loop container, it means when loop will run the Filepath variable will hold the current filepath that foreach loop container is iterating through. Now whatever task you will put inside foreach loop container that will be executed as well for each file\iteration. If you will assign the connectionstring string of flat file connection manager with FilePath, then the flat file connection manager can point to that file and that file's data can be read.

[@MVinodKumar-tt1tu]

@@learnssis Yes got it sir. So as we know enumerator which go through each file in the folder and to process with that file we are using data flow task inside the container. To read the the data of all files we used filepath in the connection string. if we map the filepath to container only it will loop through all files. otherwise without mapping i tried sir it was iterating the same file all the times. Am I correct sir?

[@learnssis]

@@MVinodKumar-tt1tu Correct.

[@amanahmed6057]

8:46 There is no Folder tab in for each loop container in 2019

[@ankbala]

At 4:34, header column names having double quotes.. how to get rid of them?

[@learnssis]

Sorry, I don't see double quotes in the Header at that location. Although if there are double quotes in the header then, while creating the connection, uncheck "column names in the first data row" | set "Text Qualifier" to ". This won't select any double quote from the file, now click on the checkbox "column names in the first data row" again, so that Header information from flat file can be used.

[@ankbala]

@@learnssis yeah..but in the next step how to do mapping with OLE DB Destination? did I miss any step?

[@learnssis]

@@ankbala If the column names in source CSV file and SQL table are same then they will be mapped automatically, otherwise on the left side, you will have columns from csv file and on the right side, you will have columns from sql table, so you can map the columns like, firstname from flat can be mapped with firstname of sql table and so on.

[@ankbala]

@@learnssis Thank you.

[@xoxoxo2024xo]

Hi buddy, thank you for sharing your experience. Even though i had difficulties to understand sometimes, i got the idea and managed to practise it by myself at the end of the day.

[@learnssis]

That sounds good. Self practice makes you perfect. Good you learnt it.

[@sriniwaaskorampalli8145]

Hi Sir, Canu please Provide Code for Insert statement in Logs through Expression... I am getting error

[@learnssis]

If you check the description of the video, in each video I have shared a link to download the files, create table statement, and code used in the SSIS package. There is a folder for each video and you can download the respective code from there.

[@sriniwaaskorampalli8145]

@@learnssis My bad sir...i have downloaded all those but dint give a look at the code.txt...thanks sir.

[@learnssis]

@@sriniwaaskorampalli8145 No problem.

[@joegodindata]

This one is difficult I got through everything and then my Foreach Loop and 'Logs' SQL Script got an error that I couldnt figure out: Error: 0xC002F210 at Logs, Execute SQL Task: Executing the query "insert into FileInfo values ('C:\Users..." failed with the following error: "Column name or number of supplied values does not match table definition.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Logs Any help would be appreciated. Moving on forward from here.

[@learnssis]

Its a SQL server related error, it means the insert query to insert the data to Fileainfo table is missing a column or having an extra column. Just double check the insert query. Open the execute SQL task. Open SQL command, don't go to expression, copy the insert query and try to run the insert query on SQL server management studio on the database where logs table FileInfo exists. You might be missing identity clause to the Id column, its my guess.

[@MD-lu6lb]

Hello there, How to generate a single flat file from multiple values from for each loop like I have 5 groups but in my out put sometimes I need single destination and sometimes multiple output . Like 5 flat files destination and sometimes one destination it depends on the requirements. Please help !

[@learnssis]

You can take a look at this video "for each loop ado enumerator" kzbin.info/www/bejne/fZ3dn4WXmraerpY You would need to select the distinct groups in an object variable using execute sql task and then you can use foreach loop container with ado enumerator, the files will be created for each group.

[@MD-lu6lb]

@@learnssis how do I populate each group in one destination using union all? Thank you!

[@learnssis]

@@MD-lu6lb If you want to use Union All then you don't need to use the foreach loop here, just use a data flow task, and then in OLE DB Source, write multiple sql queries with union all and in the destination use a flat file destination, this will fetch data from sql server according to the query whether there is a union all or not and will write the data to a flat file.

[@learnssis]

You can take a look at this example as well, where we merged data to a single CSV file. kzbin.info/www/bejne/apfRhaunjNSDf8U

[@MD-lu6lb]

@@learnssis Thank you very much!

[@mallikarjunt29]

Thanks man😊 it saved me.

[@learnssis]

Glad it worked for you.

[@viswanathponne]

really i like your ssis all videos

[@learnssis]

Thank you so much. Good to know that.

[@BTECH123]

If i executed more than 1 time rows were doubling. Could you tell me how to use delete statement in execute sql statement for this for each loop

[@learnssis]

If you don't want to double the rows, just before the for each loop container use an execute sql task, just put a truncate table statement.

[@BTECH123]

@@learnssis delete from Table right sir

[@learnssis]

@@BTECH123 yes.

[@learnssis]

@@BTECH123 truncate table would be faster.

[@BTECH123]

@@learnssis tq sir

[@abdulsami6985]

Dear Aqil , this is really useful, by any chance can we download the video as well please?

[@learnssis]

Thank you. Sure, you can download the video.

[@rahulgautam511]

Hello Aqil, As we know, we have three options in the retrieve file names. Could you please tell me how to know where we should the specific option. Generally, I used the fully qualified option. But bit confuse about rest options. 1. Name and extension. 2. Fully qualified. 3. Name only. Thanks.

[@learnssis]

Hi Rahul, yes we normally use Fully qualified to get the full path of the file. 1. however suppose you just want to move the files from one folder to another using foreach loop container then you can use Name and extension and it will just let you have only file name without file path and then based on this value you can set the value of destination file path and can move the file using File System Task. 3. Suppose you just want to grab the name of the file without extension and want to insert all file names into a sql server table then you can use Name only option.

[@rahulgautam511]

Thanks @@learnssis

[@sriniwaaskorampalli8145]

hi sir, why i am getting all data in double quotes in SQL table

[@learnssis]

Right click on the flat file connection manager, select edit | Under Text qualifier put a double quote and save the package. Now rerun the package.

[@sriniwaaskorampalli8145]

@@learnssis thanks a lot sir....got it.....

[@sagittariusguy2011]

y did u bring the ROWCOUNT in picture , is it really necessary just to confuse people .

[@learnssis]

Your point of view is correct as well. I thought to include it so that new people know that when they are loading data from different files at the same time they can capture the record count as well. But your point is valid.

[@TheSonuaujla]

Sometimes it's really helpful to learn an extra step. RowCnt is very helptul

[@dhavaldalasaniya]

It is really helpful to learn full Technics of production.

[@AshishPatel-yl1bk]

How to loop load latest file present in 10 different folder in root directory ?

[@learnssis]

Is the schema of file is same in all 10 folders ?

[@AshishPatel-yl1bk]

@@learnssis Its log file, i want loop through log files from all folder into one single table. Basically I want to read one latest file for each folder and load one by one to destination table.

[@learnssis]

@@AshishPatel-yl1bk Take a look at this to understand how to process the most recent file from a folder. kzbin.info/www/bejne/hWLbh5-Bpp2Wp7s

[@kiranraju3225]

Hi, Really nice video. Thanks for the video.

[@learnssis]

Glad to know that you liked the video. Thank you.

[@lakshmiboddu7620]

I want to display same date value into a table whatever the date available in filename .

[@learnssis]

In this video, I have shown how to get the file name from file path and insert the value into a SQL server table kzbin.info/www/bejne/bH7daZuEnJlog6c You can use a right function if the date in file is in the end to get the date or you can use substring function as well.

[@learnssis]

If you can give an example of file name then I will try to make a video on that.

[@lakshmiboddu7620]

Thank you for your response.I have many filenames like CustomerData_10.10.21_11.56_PM.csv From this file name i need date should be displayed as YYYYMMDD format.

[@learnssis]

@@lakshmiboddu7620 Okay, I will try to make a video on this one.

[@tejaswinerella5223]

Hii Aquil, I'm facing this issue Can you please help me with this Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "insert into FileInfo values ('C:\Files\CSV Files\C..." failed with the following error: "Incorrect syntax near '.3520'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Any help would be appreciated

[@learnssis]

Can you paste the code to insert data into audit_log here, it seems like there is some syntax issue with the code.

[@tejaswinerella5223]

@@learnssislearnssis Thanks for the reply I'm passing this code in Execute SQL task can you please validate it once sqlStatement: insert into FileInfo values ('C:\Files\CSV Files\Customer1.CSV',0,GETDATE()) SQLStatementSource : "insert into FileInfo values ('"+ @[User::FilePath] +"',"+ (DT_WSTR,12) @[User::cnt] +",GETDATE()) "

[@learnssis]

@@tejaswinerella5223The expression seems okay to me. When you directly execute this query on SSMS then does it fails insert into FileInfo values ('C:\Files\CSV Files\Customer1.CSV',0,GETDATE())

[@oussamaelarbi9300]

Create your FileInfo Table with this expression and try again : Create table FileInfo( Id int identity , FilePath text, RecordCount int, Dated datetime ) GO

[@EricD_192]

Great channel. Thank you!

[@learnssis]

Thank you Erick. Nice to hear that you liked the videos.

[@sangeethak725]

How to use sort inside forech container?

[@learnssis]

Hi Sangeetha, in Foreach loop container we don't have the option to load the files in an order, however I used another solution for this problem. kzbin.info/www/bejne/qKW8mqWVZ7ifhq8

[@aerobat150M]

You should take a little more time to explain what you're doing on each step. I had to stop and rewind the video MANY times to figure out what you did. Also, it was extremely difficult to figure out what you typed in the SQL Statement Source in the Logs task.

[@learnssis]

Thanks for your comment, I will try to improve the things mentioned in your comment.

[@ejbernalr]

Thanks, it's a good video.

[@learnssis]

Nice to hear from you.

[@verysecret541]

great video, but please stop saying 'You know" I have counted 'You know" 182 times. Its just too often.

[@learnssis]

Yeah, you are right. I will try to stop saying that :)

[@dilipkumar-ck1nc]

Can u write the code and update to notepad plz

[@learnssis]

Hi Dilip, I have added the code to notepad and uploaded.

[@Shankar-zi3yk]

this is not good explanation for a beginner level candidate who dont know about enumerators u are not explaining each and evry point u are doing which u know and also u are using the rowcount which uhavent completd of teaching about that which will confuse beginners like me plz explain clearly

[@learnssis]

I am really sorry Shankar, you are right I should have explained things more clearly and in more details. Recently I created one more video on the same topic as well. kzbin.info/www/bejne/eJvTkoaugr-ArtU Actually I covered every enumerator option differently like File, Enumerator, ADO enumerator, Item Enumerator, thus I have video on each enumerator type 23 Foreach loop ado enumerator in SSIS example kzbin.info/www/bejne/fZ3dn4WXmraerpY 70 Foreach loop item enumerator in ssis example kzbin.info/www/bejne/aIOwqqmApLKYnK8

[@Shankar-zi3yk]

@@learnssis I have a doubt too u have change the filepath dynamically right how it's taking

[@DG-wc8iz]

Your explanation is like.. this is Tajmahal okay then this is tower of London.. okay this is Burj Khalifa... This is Nalasopara No explanation makes video boring and hard to understand.

[@learnssis]

Thanks for your comment, yeah this video is not easy to understand for a new learner. I have added few extra steps like row count transformation and logging to it which makes the video a bit complicated for new learners. This was my very initial video so I understand this is not great. Anyway thanks for sharing your videos. I truly accept it.

[@gopalreddyguraka6769]

Ur content was good but ur too fast explanation it makes to hard and irritating to replay back

[@learnssis]

Yes Gopal you are right, in majority of videos I am too fast, I am just trying to improve this for future videos. Actually in this fast life every thing is fast 😃

[@alihaider6435]

apkay pas knowlg hai ap chaho to or zada achay andaz se smjha saktay ho ap aysay smjha rahay ho jesay k hum phalay se he sab kuch jantay hain bhater he har step ko ache tarhan explian karain or vid bananay se phalay kuch kha pe lia karain awaz me dam paida karain wesay sab kuch acha hai bus explain thora or achay se karo aysa ka jesa kesi ko sikha rahay ho ye na lagay kay phalay se he sekha hoa hai just revise kara rahay ho

[@learnssis]

Bahut bahut shukriya aake suggestions ke liye. Will take care of it.

[@alihaider6435]

@@learnssis Thanks sir for your reply

[@SurajitMetya]

For each loop container is working without """"variable mapping : user filepath😂😂😂😂

[@learnssis]

Yeah variable mapping is optional. It will run but you won't know which file it is iterating through.

[@SurajitMetya]

@@learnssis love ur video 😍😍😍😍

[@learnssis]

@@SurajitMetya Thanks so much for your kind words.

[@SurajitMetya]

@@learnssis ur linkedin I'd please

[@learnssis]

@@SurajitMetya it is www.linkedin.com/in/aqil-ahmed-01628058/

[@LazarJovovicLaki]

Your verbal skills aren't good. Going forward, you should be using text comments instead. Technically, tutorial is useful.

[@learnssis]

Thanks for your comment. Actually English is not my native language, I will try to improve it. Some people do not like reading text comments as well, will think about it.

[@TheSonuaujla]

I think Verbal skills are good. it just the sound quality of this video.

[@dhavaldalasaniya]

Your verbal skills are upto the mark to let me learn. keep doing it ..

[@sangeethak725]

How to use sort inside forech container?