locked
Unexpected behavior of Foreach Loop in SSIS 2012 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have come across the unexpected behavior of Foreach Loop in SSIS 2012. While testing package for invalid folder (path asdf:\Vishal_Work does not exists in this case) for source files. 

    I observed that the control does not enters into Foreach Loop, but also it does not fails the task. The task Foreach Loop runs successfully, though folder was invalid.

    The package did not throw any warning regarding invalid folder, but for the unmatched file pattern.

    My concern is ‘why Foreach Loop in SSIS did not failed even if folder was invalid or not exists?’

    Wednesday, February 19, 2014 11:12 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The warning is "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty."

    The warning message states the specified directory was empty, though it does not extists.

    We can handle this scenario through script task easily, but I really wonder why Foreach loop does not validate the folder. It is loophole in Foreach loop?

    I would not classify it as loophole. I actually agree with Russ, it was a design decision on the side of the development team.

    But you may freely file it as a bug or suggestion at MS Connect: https://connect.microsoft.com

    I would vote it up because I see how it can lead to undesired package behavior (logical flaw).

    Arthur My Blog

    • Proposed as answer by Mike Yin Wednesday, February 26, 2014 8:11 AM
    • Marked as answer by Vishal Karve Thursday, February 27, 2014 3:01 AM
    Thursday, February 20, 2014 2:52 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    AFAIK a ForEach Loop set to iterate over a folder does fail in case there is no such folder or path.

    Question is how to reproduce your issue?

    Arthur My Blog

    Wednesday, February 19, 2014 3:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Here are the steps to reproduce the issue.

    1. Add a new package, name it as "ForEachLoop".
    2. Add a ForEach loop container
    3. Double click on ForEach Loop container, go to the "Collection" tab.
    4. In the "Enumerator" select "Foreach File Enumerator".
    5. Now in the "Enumerator Configuration", select the path of your source folder. (have some folder path which does not exists)
    6. In "Files", specify the file type as "*.*"
    7. This will configure your ForEach loop container
    8. Execute the package, the package did not fail.
    9. The package did not throw any warning regarding invalid folder, but for the unmatched file pattern.

    Wednesday, February 19, 2014 5:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    That is correct.  You would need to add a script task to validate the directory.  The foreach loop simply finds nothing to do when the directory does not exist.

    • Proposed as answer by Mike Yin Wednesday, February 26, 2014 8:11 AM
    • Unproposed as answer by Vishal Karve Thursday, February 27, 2014 3:00 AM
    Wednesday, February 19, 2014 5:25 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    After this issue, I have added script task to validate folder before Foreach loop. But why Foreach loop does not throw error/warning regarding folder does not exists?
    Wednesday, February 19, 2014 5:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is certainly an area where Microsoft chose one out of several possible ways to handle this situation.  I had never thought of this problem, but I think that what I would consider ideal would be some property that I could set that would determine how to handle the missing folder.  I am uncomfortable in returning an error and failing the task if the folder doesn't exist.  I would prefer to roll my own handler for the missing folder using a script component over failing the task.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Mike Yin Wednesday, February 26, 2014 8:11 AM
    • Unproposed as answer by Vishal Karve Thursday, February 27, 2014 3:01 AM
    Wednesday, February 19, 2014 7:15 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    AFAIK a ForEach Loop set to iterate over a folder does fail in case there is no such folder or path.

    Question is how to reproduce your issue?

    Arthur My Blog


    I was not right, there is only a warning, perhaps you can leverage this fact in your package e.g. check its log.

    Arthur My Blog

    Wednesday, February 19, 2014 9:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The warning is "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty."

    The warning message states the specified directory was empty, though it does not extists.

    We can handle this scenario through script task easily, but I really wonder why Foreach loop does not validate the folder. It is loophole in Foreach loop?

    Thursday, February 20, 2014 7:11 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The warning is "The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty."

    The warning message states the specified directory was empty, though it does not extists.

    We can handle this scenario through script task easily, but I really wonder why Foreach loop does not validate the folder. It is loophole in Foreach loop?

    I would not classify it as loophole. I actually agree with Russ, it was a design decision on the side of the development team.

    But you may freely file it as a bug or suggestion at MS Connect: https://connect.microsoft.com

    I would vote it up because I see how it can lead to undesired package behavior (logical flaw).

    Arthur My Blog

    • Proposed as answer by Mike Yin Wednesday, February 26, 2014 8:11 AM
    • Marked as answer by Vishal Karve Thursday, February 27, 2014 3:01 AM
    Thursday, February 20, 2014 2:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Arthur,

    Thanks for your reply, I have filed a bug. Bud ID: 820599

    Friday, February 21, 2014 6:25 AM