I have a table (FilesToCopy
) with a list of filenames.
id | filename |
---|---|
1 | c:\temp\file1.txt |
2 | c:\temp\file2.txt |
3 | c:\temp\file2.txt |
One by one the files get copied to another location (eg c:\test
).
I can loop through this table easy enough to execute a copy command for each file.
That is just an Execute SQL Task reading the table, putting the results in an object variable, followed by a For Each container.
But what if, during the execution of the for each loop, records get added to the table FilesToCopy
?
Imagine that during the for each loop the file c:\temp\file99.txt gets added to the table FilesToCopy
id | filename |
---|---|
1 | c:\temp\file1.txt |
2 | c:\temp\file2.txt |
3 | c:\temp\file2.txt |
99 | c:\temp\file99.txt |
Can I somehow tell the for each loop that it also has to copy the file c:\temp\file99.txt even though the file was not there at the beginning of the loop?
So the effect would be:
FilesToCopy
is read, a loop is started.c:\temp\file1.txt
is copied to c:\test
c:\temp\file1.txt
is deleted from the table FilesToCopy
c:\temp\file2.txt
is copied to c:\test
c:\temp\file2.txt
is deleted from the table FilesToCopy
c:\temp\file99.txt
is added to the table FilesToCopy
c:\temp\file3.txt
is copied to c:\test
c:\temp\file3.txt
is deleted from the table FilesToCopy
c:\temp\file99.txt
is copied to c:\test
c:\temp\file99.txt
is deleted from the table FilesToCopy
FilesToCopy
So what I would like to do do is somehow refresh the list that was fed to the loop when it started.
I hope this is clear.
I'd use a do while loop instead.
Have a variable called moreToProcess data type boolean.
Set the value before entering the loop using execute SQL statement based on count > 0
First step in the loop is to get a record to process using exec SQL.
Before exiting loop rerun the SQL to reset the variable.
Set to loop while moreToProcess is true.