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.
Hi, so does it pick the file name(s) from the traversed folders and maps to a variable?
ReplyDeleteNo 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.
DeleteOr do you neef a combined folder and file enumerator?
DeleteCombined please, with an ability to set a custom mask at the folder only level.
ReplyDeleteThanks. Will think about it... for now you have to combine the two enumerators to accomplish your request.
DeleteHi are you able to use unc paths?
ReplyDeleteI tested it with d:\folder\subfolder\ and \\server\share\folder\
Deletehow to avoid zip folders in the subfolders,when forming xml
ReplyDeleteI 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"
DeleteWill publish an update if I can replicate it and when I have a solution for it.
Thank you Joost for your quick response, helped me lot
Deletei have one moreQuestion, how to include parent folder along with subfolders.
DeleteThanks
There is a ckeckbox "include rootfoler"
DeleteHi Joost,
ReplyDeleteActually i am using standard for each file enumunator, then how to send parent folder along with childfolder.
Thanks for your response
@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/
Deletesorry for asking again,
ReplyDeletei cann't see "include rootfolder" checkbox in my foreachloop container
Thanks
@Anonymous: Which version of SSIS are you using and did you install the custom enumerator? Also see the screenshots above. Any differences?
DeleteThank you for your response,
ReplyDeleteI 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.
Please post the comments on the right article: http://microsoft-ssis.blogspot.com/2011/01/foreach-folder-enumerator.html
DeleteHi this looks really good, but we're stuck on SQL 2005. Is this tested / compatible with SQL 2005?
ReplyDeleteSorry, 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.
DeleteRegards,
Joost
Joost, using the Folder enumerator, how can I store a subfolder name in a variable.
ReplyDeleteI have Folder1, Folder2, Folder3. I want to return the largest folder number(folder3) and store it in a variable. Thanks
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.
DeleteIf that's not the case then you need a Script Task with some .Net code to do that for you.
Is a 2014 version in the works?
ReplyDeleteSorry, 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.
DeleteIs the 2014 version still being worked on? Download page lists it as soon.
ReplyDeleteHi Luke K,
DeleteSorry 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.
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
ReplyDeleteMake sure you install both 32bit and 64bit. And more important, make sure you install it on the same drive as SQL Server.
DeleteJoost,
ReplyDeleteCan 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.