▼

Friday, 20 January 2012

Custom SSIS Component: Foreach Sorted File Enumerator

Last year I did a post on how to create a Foreach Sorted File 'Enumerator' with a Foreach ADO Enumerator and a preceding Script Task that created a sorted collection.
A sorted Foreach Loop Container




















But Microsoft.Net colleague Marc Potters helped me to create a real enumerator. The SSIS Foreach Sorted File Enumerator loops through the files in a folder like a regular file enumerator, but now you can sort  it (name and/or date)! How handy is that?
Foreach Sorted File Enumerator
























The 2012 version also has regular expression support.
2012 version Regex Support
























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.

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



























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

23 comments:

  1. WoundedEgo23 December 2014 at 22:57

    If I install this on my local machine and use the custom task to create a package will the custom task need to be installed on the server from which it is run in production?

    ReplyDelete
    Replies
    1. Joost van Rossum23 December 2014 at 23:00

      Yes it should also be installed on the production server where the package is running

      Delete
  2. Anonymous12 February 2015 at 16:48

    I love this. However, I have run into one issue. When I assign a folder under Enumerator configuration, it works great. When I use a variable to assign the directory dynamically as an expression, I get "Directory is mandatory". Any ideas?

    ReplyDelete
    Replies
    1. Joost van Rossum12 February 2015 at 19:08

      You could fill in a dummy folder "d:\" and then override it with an expression on the Directory property in the Collection tab of the enumerator

      Delete
  3. Anonymous12 February 2015 at 20:36

    I believe I tried exactly what you said. I used a dummy folder "d:\" and then used a variable to override the Directory property on the Collection tab. Once I added the expression, the dummy folder disappeared, which I didn't think was going to be a problem because that is the same thing that happens for the regular Foreach File Enumerator. However, when I try running the package, I get the Directory is mandatory error. This error only happens when I try overriding the Directory property.

    ReplyDelete
    Replies
    1. Joost van Rossum12 February 2015 at 20:49

      Is the expression always returning a valid folder? If you are using a variable in the expression then it should contain a valid value. You could also try setting the DelayValidation property on true if it doesn't contain a valid value on designtime.

      Delete
  4. Anonymous12 February 2015 at 23:08

    The DelayValidation property worked perfectly. Thank you! This component now works like a dream.

    ReplyDelete
  5. Unknown26 February 2016 at 13:42

    Hi

    it's great.

    Additional question: Can we get the creation date also as an additional parameter to use it in the programme ? That would be most useful. Thanks Klaus

    ReplyDelete
    Replies
    1. Joost van Rossum18 March 2016 at 15:59

      Do you mean like an extra filter to get only files where the create date is between two dates or where the create date is before/after a certain date?

      Delete
    2. Unknown18 March 2016 at 16:07

      Good idea, but i just meant a possibillity to catch the value of the date field. You sort the date, but i've so far no chance to get the value itself and use it in the program.

      Delete
    3. Joost van Rossum18 March 2016 at 16:36

      Great idea. Then you can fill an extra variable in the Foreach Loop Variable Mappings pane that contains the creation or modified date of the current file.
      Will think about this and do some testing. Could take a while because I first need to create 2016 versions of all my components before I start adding new features.

      Delete
    4. Unknown18 March 2016 at 16:47

      Thanks a lot. I easily can wait ;-)

      Delete
  6. Matthew Lehn29 December 2016 at 16:44

    Will this work with VS 2015/SQL 2016? If not, do you intend on making a compatible version?

    ReplyDelete
    Replies
    1. Joost van Rossum29 December 2016 at 18:51

      Yes it works in 2016. Install both 32 and 64 bit on the same drive as SQL Server is installed.

      Delete
  7. Unknown21 March 2017 at 16:59

    Hi Joost
    I have got a problem with Bids 2015 and ssdt, installing all the dll, I verify their installation in all the SQL versions. But i cannot see the component in the toolbox. I try targetting all the 2012,2014,2016, but i never see the enumeration.
    Have you an idea please ?
    thanks a lot for your great job
    David

    ReplyDelete
    Replies
    1. Joost van Rossum21 March 2017 at 20:21

      1) Make sure you install the 32bit version for Visual Studio (and the 64bit version for SQL Server)
      2) Make sure you install on the same drive as you installed SQL Server
      3) Check the folder [drive]:\Program Files\Microsoft SQL Server\130\DTS\ForEachEnumerators\ (110=2012,120=2014 and 130=2016). Do you see my enumerators and those from Microsoft?

      Delete
    2. Joost van Rossum21 March 2017 at 23:24

      And they won't be in the toolbox, but in the foreach loop container as an option in the drop down field.

      Delete
    3. Unknown22 March 2017 at 10:47

      Thanks Joost, the solution was in you last sentence, I was focusing on the creation of a new dedicated container but in fact you have right to "just" add an enumerator inside the foreach loop container keeping all the others properties of the container, just great ;)

      Delete
  8. Anonymous10 January 2018 at 16:12

    Hi,

    I downloaded & installed the installer as suggested in the article. My machine is 64-bit version, I installed as mentioned to select "C:\Program Files\Microsoft SQL Server\110\DTS\" as it is 2012. But After refreshing I am unable to see this enumerator in the list.

    ReplyDelete
    Replies
    1. Joost van Rossum10 January 2018 at 16:35

      You also need to install the 32bit version for visual Studio.

      Delete
    2. Anonymous11 January 2018 at 10:09

      Thank you. It worked

      Delete
  9. Edo van Loon28 October 2019 at 11:48

    Hi Joost,

    Is it possible to make the enumerator work with VS 2017 with SSDT? I can open my 2012 packages in it, but the ones with your enumerator fail.

    Thanks,

    Edo.

    ReplyDelete
  10. Edo van Loon28 October 2019 at 12:05

    Hi Joost,

    Is it possible to install the enumerator with VS2017 with SSDT?

    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.