Foreach Loop Container with Foreach File Enumerator option iterates all files twice

Question

I am using the SSIS Foreach Loop Container to iterate through files with a certain pattern on a network share.

I am encountering an kind of unreproducible malfunction of the Loop Container:

Sometimes the loop is executed twice. After all files were processed it starts over with the first file.

Have anyone encountered a similar bug? Maybe not directly using SSIS but accessing files on a Windows share with some kind of technology? Could this error relate to some network issues?

Thanks.

m1dst · Accepted Answer · 2015-06-10 08:52:31Z

1

I found this to be the case whilst working with Excel files and using the *.xlsx wildcard to drive the foreach.

Once I put logging in place I noticed that when the Excel was opened it produced an excel file prefixed with ~$. This was picked up by the foreach loop.

So I used a trick similar to http://geekswithblogs.net/Compudicted/archive/2012/01/11/the-ssis-expression-wayndashskipping-an-unwanted-file.aspx to exclude files with a ~$ in the filename.

answered Jun 10, 2015 at 8:52

m1dst

3332 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcus D · Accepted Answer · 2011-08-03 10:22:42Z

What error message (SSIS log / Eventvwr messages) do you get?

Similar to @Siva, I've not come across this, but some ideas you could use to try and diagnose. You may be doing some of these already, I've just written them down for completeness from my thought processes...

log all files processed. write a line to a log file/table pre-processing (each file), then post-process (each file). Keep the full path of each file. This is actually something we do as standard with our ETL implementations, as users are often coming back to us with questions about when/what has been loaded. This will allow you to see if files are actually being processed twice.
perhaps try moving each file after it is processed to a different directory. That will make it more difficult to have a file processed a second time and the problem may disappear. (If you are processing them from an area that is a "master" area (and so cant move them), consider copying the files to a "waiting" folder, then processing them and moving them to a "processed" folder)
@Siva's comment is interesting - look at the "traverse subfolders" check box.
check your eventvwr for odd network events, or application events (SQL Server restarting?)
use perf mon to see if there is anything odd happening in terms of network load on your server (a bit of a random idea!)
try running your whole process with files on a local disk instead of a network disk, if your mean time between failures is after running 10 times, then you could do this load locally 20-30 times and if you dont get an error it may be a network error

bes · Accepted Answer · 2011-08-23 10:54:15Z

0

nothing helped - I implemented following workaround: script task in the foreach iterator which tracks all files. if a file was alread loaded a warning is fired and the file is not processed again. anyway, seems to be some network related problem...

answered Aug 23, 2011 at 10:54

bes

1259 bronze badges

Collectives™ on Stack Overflow

Foreach Loop Container with Foreach File Enumerator option iterates all files twice

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related