Search code examples
t-sqlssisfile-copyingsql-server-2019ssis-2019

Loop through table while records keep getting added to the table


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:

  1. table FilesToCopy is read, a loop is started.
  2. c:\temp\file1.txt is copied to c:\test
  3. The record for c:\temp\file1.txt is deleted from the table FilesToCopy
  4. c:\temp\file2.txt is copied to c:\test
  5. The record for c:\temp\file2.txt is deleted from the table FilesToCopy
  6. In the meanwhile c:\temp\file99.txt is added to the table FilesToCopy
  7. c:\temp\file3.txt is copied to c:\test
  8. The record for c:\temp\file3.txt is deleted from the table FilesToCopy
  9. c:\temp\file99.txt is copied to c:\test
  10. The record for c:\temp\file99.txt is deleted from the table FilesToCopy
  11. Loop ends when there are no more records in table 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.


Solution

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