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.
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
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.
@@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?
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.
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.
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.
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 !
You can take a look at this video "for each loop ado enumerator" th-cam.com/video/GlznRaeQkx4/w-d-xo.html 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 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.
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 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.
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.
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.
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.
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. th-cam.com/video/rtYir_2SlPM/w-d-xo.html
@@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.
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.
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
@@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()) "
@@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())
Create your FileInfo Table with this expression and try again : Create table FileInfo( Id int identity , FilePath text, RecordCount int, Dated datetime ) GO
In this video, I have shown how to get the file name from file path and insert the value into a SQL server table th-cam.com/video/6Mz8hNg45ME/w-d-xo.html 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.
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.
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 😃
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.
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.
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
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
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. th-cam.com/video/BjpaSxMZMxs/w-d-xo.html 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 th-cam.com/video/GlznRaeQkx4/w-d-xo.html 70 Foreach loop item enumerator in ssis example th-cam.com/video/2RMyvJoMefM/w-d-xo.html
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.
![ทุกครั้งที่หลับตา (Lucid Dream) - AYLA's [ Official MV ]](http://i.ytimg.com/vi/4i1OBAQyv58/mqdefault.jpg)
![For Loop Container in SSIS Example [Real Time Example]](/img/n.gif)
Thank you for your sharing about how to use the Foreach Loop via Example.
You are most welcome.
It's really nice explaining for beginners...Thank You
Thank you Sowmya.
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.
That sounds good. Self practice makes you perfect. Good you learnt it.
really i like your ssis all videos
Thank you so much. Good to know that.
Thanks man😊 it saved me.
Glad it worked for you.
Hi,
Really nice video. Thanks for the video.
Glad to know that you liked the video. Thank you.
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
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.
@@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?
@@MVinodKumar-tt1tu Correct.
Dear Aqil , this is really useful, by any chance can we download the video as well please?
Thank you. Sure, you can download the video.
Great channel. Thank you!
Thank you Erick. Nice to hear that you liked the videos.
Thanks, it's a good video.
Nice to hear from you.
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
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.
@@learnssis delete from Table right sir
@@BTECH123 yes.
@@BTECH123 truncate table would be faster.
@@learnssis tq sir
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.
Thanks for your comment, I will try to improve the things mentioned in your comment.
8:46
There is no Folder tab in for each loop container in 2019
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.
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.
Thanks @@learnssis
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 !
You can take a look at this video "for each loop ado enumerator"
th-cam.com/video/GlznRaeQkx4/w-d-xo.html
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.
@@learnssis how do I populate each group in one destination using union all? Thank you!
@@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.
You can take a look at this example as well, where we merged data to a single CSV file.
th-cam.com/video/4fnTxqWoPIc/w-d-xo.html
@@learnssis Thank you very much!
At 4:34, header column names having double quotes.. how to get rid of them?
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.
@@learnssis yeah..but in the next step how to do mapping with OLE DB Destination? did I miss any step?
@@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.
@@learnssis Thank you.
Hi Sir,
Canu please Provide Code for Insert statement in Logs through Expression... I am getting error
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.
@@learnssis My bad sir...i have downloaded all those but dint give a look at the code.txt...thanks sir.
@@sriniwaaskorampalli8145 No problem.
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.
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.
How to use sort inside forech container?
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.
th-cam.com/video/rtYir_2SlPM/w-d-xo.html
great video, but please stop saying 'You know" I have counted 'You know" 182 times. Its just too often.
Yeah, you are right. I will try to stop saying that :)
hi sir, why i am getting all data in double quotes in SQL table
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.
@@learnssis thanks a lot sir....got it.....
How to loop load latest file present in 10 different folder in root directory ?
Is the schema of file is same in all 10 folders ?
@@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.
@@AshishPatel-yl1bk Take a look at this to understand how to process the most recent file from a folder.
th-cam.com/video/O1xVlKq8cqY/w-d-xo.html
y did u bring the ROWCOUNT in picture , is it really necessary just to confuse people .
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.
Sometimes it's really helpful to learn an extra step. RowCnt is very helptul
It is really helpful to learn full Technics of production.
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
Can you paste the code to insert data into audit_log here, it seems like there is some syntax issue with the code.
@@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()) "
@@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())
Create your FileInfo Table with this expression and try again : Create table FileInfo(
Id int identity ,
FilePath text,
RecordCount int,
Dated datetime
)
GO
I want to display same date value into a table whatever the date available in filename .
In this video, I have shown how to get the file name from file path and insert the value into a SQL server table
th-cam.com/video/6Mz8hNg45ME/w-d-xo.html
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.
If you can give an example of file name then I will try to make a video on that.
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.
@@lakshmiboddu7620 Okay, I will try to make a video on this one.
Can u write the code and update to notepad plz
Hi Dilip, I have added the code to notepad and uploaded.
Ur content was good but ur too fast explanation it makes to hard and irritating to replay back
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 😃
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.
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.
For each loop container is working without """"variable mapping : user filepath😂😂😂😂
Yeah variable mapping is optional. It will run but you won't know which file it is iterating through.
@@learnssis love ur video 😍😍😍😍
@@SurajitMetya Thanks so much for your kind words.
@@learnssis ur linkedin I'd please
@@SurajitMetya it is
www.linkedin.com/in/aqil-ahmed-01628058/
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
Bahut bahut shukriya aake suggestions ke liye. Will take care of it.
@@learnssis Thanks sir for your reply
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
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.
th-cam.com/video/BjpaSxMZMxs/w-d-xo.html
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
th-cam.com/video/GlznRaeQkx4/w-d-xo.html
70 Foreach loop item enumerator in ssis example
th-cam.com/video/2RMyvJoMefM/w-d-xo.html
@@learnssis I have a doubt too u have change the filepath dynamically right how it's taking
Your verbal skills aren't good. Going forward, you should be using text comments instead. Technically, tutorial is useful.
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.
I think Verbal skills are good. it just the sound quality of this video.
Your verbal skills are upto the mark to let me learn. keep doing it ..
How to use sort inside forech container?