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. 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. Yes it should also be installed on the production server where the package is running

      Delete
  2. 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. 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. 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. 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. The DelayValidation property worked perfectly. Thank you! This component now works like a dream.

    ReplyDelete
  5. 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. 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. 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. 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. Thanks a lot. I easily can wait ;-)

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

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

      Delete
  7. 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. 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. And they won't be in the toolbox, but in the foreach loop container as an option in the drop down field.

      Delete
    3. 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. 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. You also need to install the 32bit version for visual Studio.

      Delete
    2. Thank you. It worked

      Delete
  9. 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. 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.

Related Posts Plugin for WordPress, Blogger...