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.


































Geplaatst door Joost van Rossum
Labels: , , , ,

29 comments:

  1. Anonymous3 February 2012 at 17:34

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

    ReplyDelete
    Replies
    1. Joost van Rossum3 February 2012 at 19:06

      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. Joost van Rossum3 February 2012 at 19:34

      Or do you neef a combined folder and file enumerator?

      Delete
  2. Anonymous3 February 2012 at 19:50

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

    ReplyDelete
    Replies
    1. Joost van Rossum3 February 2012 at 21:36

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

      Delete
  3. Anonymous25 July 2012 at 05:18

    Hi are you able to use unc paths?

    ReplyDelete
    Replies
    1. Joost van Rossum25 July 2012 at 08:53

      I tested it with d:\folder\subfolder\ and \\server\share\folder\

      Delete
  4. Anonymous16 August 2012 at 04:21

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

    ReplyDelete
    Replies
    1. Joost van Rossum16 August 2012 at 22:59

      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. Anonymous17 August 2012 at 03:38

      Thank you Joost for your quick response, helped me lot

      Delete
    3. Anonymous17 August 2012 at 04:08

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

      Delete
    4. Joost van Rossum17 August 2012 at 06:43

      There is a ckeckbox "include rootfoler"

      Delete
  5. Anonymous17 August 2012 at 16:37

    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. Joost van Rossum17 August 2012 at 20:58

      @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. Anonymous17 August 2012 at 23:14

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

    ReplyDelete
    Replies
    1. Joost van Rossum17 August 2012 at 23:28

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

      Delete
  7. Anonymous18 August 2012 at 18:46

    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. Alex9 November 2012 at 17:43

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

    ReplyDelete
    Replies
    1. Joost van Rossum9 November 2012 at 20:25

      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. Unknown12 July 2013 at 17:07

    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. Joost van Rossum12 July 2013 at 17:52

      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. Luke K8 December 2015 at 21:39

    Is a 2014 version in the works?

    ReplyDelete
    Replies
    1. Joost van Rossum18 March 2016 at 14:53

      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. Luke K17 December 2015 at 01:26

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

    ReplyDelete
    Replies
    1. Joost van Rossum18 March 2016 at 14:53

      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. priyanka20 December 2016 at 20:05

    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. Joost van Rossum21 December 2016 at 08:46

      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. Edo van Loon28 October 2019 at 15:16

    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.

Subscribe to: Post Comments (Atom)
Related Posts Plugin for WordPress, Blogger...