foreach loop iteration

Question

Hi,

I have designed one package where i use foreachloop container task. I have one directory which has 4 txt files and i am reading those files and dump into sql server table. But i want to read only one file at a time. When i excute that package it reads all four files and at the end i have used file system task which move those files from one dir to another dir. When source dir is empty, it stops lopping proces. I do not want that one. As i said i want to read or get only one file at a time and move to specified dir. How can i do it?.

Thanks,

Shalin

Answer 1

And which one? Just a random one? The oldest/newest?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 2

newest one. I mean if there are 4 files already in that folder and if i want to read them one by one. it should be read 1st file, process package and then it should stop. it should not look for next file at the same time. What is happening now... when i run it, it reads all 4 four files and moved it to archive dir.

Make sense?

Shalin

Answer 3

@SSISJoost: Nice One :)

@Shalin83: Correct me if i am wrong. you are getting the file name in a variable in the for each loop right and you must be using this variable to read the file and once it is dumped in SQL move it to other location using a dataflow and file system task inside the For each Loop

---

--------------------------------------------------------

Surender Singh Bhadauria

 

Answer 4

newest one. I mean if there are 4 files already in that folder and if i want to read them one by one. it should be read 1st file, process package and then it should stop. it should not look for next file at the same time. What is happening now... when i run it, it reads all 4 four files and moved it to archive dir.

Make sense?

Shalin

Not really :-) But there are 4 files and you want to run the package 4 times and process one file at a time...
I don't think the foreach loop file enumerator is the best choice here, because you're processing 1 file only and you want the newest file but the foreach loop file enumerator can't be sorted on date.

 

An alternative could be a Script Task to fill a variable with the newest file and use that variable in an expression of a Connection Manager.

So 1 Script Task and 1 Data Flow Task and 1 File System Task and no Foreach loop container.

How is your VB.Net or C#?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 5

Here is a C# example (not tested yet):

// Get all files within the folder 
string[] allFiles = Directory.GetFiles(Dts.Variables["User::yourFolder"].Value.ToString()); 

// Variable for storing file properties 
FileInfo fileInfo; 

String myNewestFile;
Datetime creationdate;

// Loop through the files in the folder 
foreach (string currentFile in allFiles) 
{ 
 // Fill fileInfo variable with file information 
 fileInfo = new FileInfo(currentFile); 

 // Check if the currentfile is the latest
 if (fileInfo.CreationTime > creationdate)
 {
  // If the latest fill the variables
  creationdate = fileInfo.CreationTime;
  myNewestFile = fileInfo.FullName
 }
} 
Dts.Variables["NewestFilepath"].Value = myNewestFile;

I still don't get the point for only one file at the time. Maybe this sorted file enumerator is an alternative: http://microsoft-ssis.blogspot.com/2011/04/how-to-configure-foreach-loop-container.html 

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 6

@surender - Yes that is excatly i am doing.

@SSISJoost - Yes i use the variable to read the files. Lets forget out newest. Let me expmain you better.

I have one folder and there are files file1,file2,file3,file4 ok.

What is happening now when i run it, it gets first file dump into sql and move that file to diff folder. So now another folder has file1 and it keeps repeating this process until source folder is empty. dopes not stop.

what i want.... when i run it should get file1 from source folder, dumo data in to sql and moved that file to dest folder and then it should stop.

when i run it again it should read file2 and so on.

It should go in sequence. Does not matter with newest or oldest.

I hope this will help you to understand my req.

 

Shalin

Answer 7

Then don't use a Foreach Loop Container.  You don't want your actions executed FOR EACH file - you only want them executed FOR ONE file.  Follow Joost's advice.

---

Talk to me now on

Answer 8

Hi,

I have designed one package where i use foreachloop container task. I have one directory which has 4 txt files and i am reading those files and dump into sql server table. But i want to read only one file at a time. When i excute that package it reads all four files and at the end i have used file system task which move those files from one dir to another dir. When source dir is empty, it stops lopping proces. I do not want that one. As i said i want to read or get only one file at a time and move to specified dir. How can i do it?.

Thanks,

Shalin

Do you have your file system task in the foreachloop our outside the foreach loop. If you have it outside the foreach loop just the last file will be moved and not all of them one after the other.

---

My Blog    |    Ask Me    |     Test your SSIS skills

Answer 9

@SUDEEP - I am not able to use file system task outside of loop becaus i have used source variable which is related with foreachloop container.

Guys thanks for all ans....

but is there any other way?

i am not good with vb.net or C# so i do not know how to write code for that.

Thanks,

shalin