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.
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?
ReplyDeleteYes it should also be installed on the production server where the package is running
DeleteI 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?
ReplyDeleteYou 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
DeleteI 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.
ReplyDeleteIs 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.
DeleteThe DelayValidation property worked perfectly. Thank you! This component now works like a dream.
ReplyDeleteHi
ReplyDeleteit'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
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?
DeleteGood 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.
DeleteGreat 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.
DeleteWill 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.
Thanks a lot. I easily can wait ;-)
DeleteWill this work with VS 2015/SQL 2016? If not, do you intend on making a compatible version?
ReplyDeleteYes it works in 2016. Install both 32 and 64 bit on the same drive as SQL Server is installed.
DeleteHi Joost
ReplyDeleteI 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
1) Make sure you install the 32bit version for Visual Studio (and the 64bit version for SQL Server)
Delete2) 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?
And they won't be in the toolbox, but in the foreach loop container as an option in the drop down field.
DeleteThanks 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 ;)
DeleteHi,
ReplyDeleteI 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.
You also need to install the 32bit version for visual Studio.
DeleteThank you. It worked
DeleteHi Joost,
ReplyDeleteIs 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.
Hi Joost,
ReplyDeleteIs it possible to install the enumerator with VS2017 with SSDT?
Regards,
Edo