Wednesday, 1 February 2012

Custom SSIS Component: Foreach Folder Enumerator


About a year ago I did a post on creating a Foreach Folder Enumerator with a Script Task.
Foreach Folder Enumerator with Script Task














Now I have created a real Foreach Folder Enumerator and you can even sort on name and date. There is also an option to include the root folder (if it passes the filter) and the standard Traverse Subfolders is also included. In version 1.1 has regular expression support.
Foreach Folder Enumerator V1.1






























Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.
Version 1.1: Regular Expression support
Version 1.2: 2014 and 2016 version added, Option to fail if folder is empty. Upgradable

Installation
The installer registers the DLL in the GAC and copies it to the ForEachEnumerators folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

SSIS
The enumerator is automatically added to the Foreach Loop when you open a SSIS project.
The new enumerator V1.0



























Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom Enumerator.


































29 comments:

  1. Hi, so does it pick the file name(s) from the traversed folders and maps to a variable?

    ReplyDelete
    Replies
    1. No this Folder enumerator only returns folder paths/names. I think you need the File Enumerator. The standard File enumerator also has a traverse folder option and our Sorted File enumerator has that same option.

      Delete
    2. Or do you neef a combined folder and file enumerator?

      Delete
  2. Combined please, with an ability to set a custom mask at the folder only level.

    ReplyDelete
    Replies
    1. Thanks. Will think about it... for now you have to combine the two enumerators to accomplish your request.

      Delete
  3. Hi are you able to use unc paths?

    ReplyDelete
    Replies
    1. I tested it with d:\folder\subfolder\ and \\server\share\folder\

      Delete
  4. how to avoid zip folders in the subfolders,when forming xml

    ReplyDelete
    Replies
    1. I haven't tested zip folder yet, but for now you could add a dummy task within the foreach loop and connect it to your first task. Add an expression on the Precedence Constraint between them that tests for .zip in the foldername. Something like: LOWER(RIGHT(@[User::FolderPath),4)) == ".zip"

      Will publish an update if I can replicate it and when I have a solution for it.

      Delete
    2. Thank you Joost for your quick response, helped me lot

      Delete
    3. i have one moreQuestion, how to include parent folder along with subfolders.
      Thanks

      Delete
    4. There is a ckeckbox "include rootfoler"

      Delete
  5. Hi Joost,

    Actually i am using standard for each file enumunator, then how to send parent folder along with childfolder.

    Thanks for your response

    ReplyDelete
    Replies
    1. @Anonymous: I'm not sure what you mean, but please use the SSIS MSDN Forum for general SSIS questions. I'm a regular reader of that forum: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/threads/



      Delete
  6. sorry for asking again,
    i cann't see "include rootfolder" checkbox in my foreachloop container
    Thanks

    ReplyDelete
    Replies
    1. @Anonymous: Which version of SSIS are you using and did you install the custom enumerator? Also see the screenshots above. Any differences?

      Delete
  7. Thank you for your response,
    I am using 2008 version, i don't have permissions to install custom enumerator.
    Actually i am looking some change in the script task (to include parent folder) which you provide for looping over subfolders, the output of the xml path contains only subfolders.

    ReplyDelete
  8. Hi this looks really good, but we're stuck on SQL 2005. Is this tested / compatible with SQL 2005?

    ReplyDelete
    Replies
    1. Sorry, I only have a 2008 and 2012 version. But check the link at the top of this page for alternative solutions that also work for 2005.

      Regards,

      Joost

      Delete
  9. Joost, using the Folder enumerator, how can I store a subfolder name in a variable.
    I have Folder1, Folder2, Folder3. I want to return the largest folder number(folder3) and store it in a variable. Thanks

    ReplyDelete
    Replies
    1. If you want to have to folder with the highest number in it you could sort the loop ASC and then when the loop is finished the variable will contain the last folder with the highest number. It's only possible if the numbers have prefixes 001, 002, 003, etc. or don't have numbers higher then 9.

      If that's not the case then you need a Script Task with some .Net code to do that for you.

      Delete
  10. Is a 2014 version in the works?

    ReplyDelete
    Replies
    1. Sorry, finally had a change to add the 2014 and 2016 version. Also added new option to let the enumerator fail if the folder is empty.

      Delete
  11. Is the 2014 version still being worked on? Download page lists it as soon.

    ReplyDelete
    Replies
    1. Hi Luke K,

      Sorry for the late replay, finally had a change to add the 2014 and 2016 version. Also added new option to let the enumerator fail if the folder is empty.

      Delete
  12. This didn't work for me. I don't see a 'foreach folder' enumerator added in SSDT. I tried on both SQL Server 2012 and 2014

    ReplyDelete
    Replies
    1. Make sure you install both 32bit and 64bit. And more important, make sure you install it on the same drive as SQL Server.

      Delete
  13. Joost,

    Can the sorted enumerator be installed with VS2017 SSDT. I can open VS2012 packages in it, but can't get it ot work with your add-ons.

    Regards,

    Edo.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.