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.

Custom SSIS Component: Foreach FTP File Enumerator

A while ago I did a post on how to create a Foreach FTP File 'Enumerator' with a Script Task and a Foreach Variable Enumerator.

Foreach FTP File 'Enumerator'

But Microsoft.Net colleague Marc Potters helped me to create a real enumerator. The SSIS Foreach FTP File Enumerator loops through the files on a FTP site. You can sort, filter and traverse subfolders. It will use one of the FTP Connections from the Connection Managers.

Foreach FTP File Enumerator

Foreach FTP File Enumerator 1.4

How to use it


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: Minor bug fixes
Version 1.2: Added Regular Expression filter
Version 1.3: Fix sorting bug and added possibility to add a connection manager
Version 1.4: Added option to fail on empty enumerator and option to return either filename/extension or complete path. Added 2016 version and made 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

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

Custom SSIS Component: File-in-use Task

A while ago a did a post about waiting until a source file is unlocked. This script prevents immediately failure if the file is still locked by an other process (or user).

Script Task

Microsoft.Net colleague Marc Potters helped me to create a custom task for it, which should make thinks easier for those who have less programming experience.

File-in-use task

The SSIS File-in-use Task prevents immediately failure if the file is still locked by an other process (or user). It checks every X seconds if the lock is gone. You can specify the number of checks until failure and the time between the checks. You can either check one of the appropriate connectionmanagers or a string variable that contains a filepath.


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.
V1.0: initial version 2008-2012
V1.1: Added 2014-2016, ability to add connection manager, property for debug info, upgradable. Added 64 bit installers

Installation
The installer registers the DLL in the GAC and copies it to the task folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\Tasks\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

How add the task the the toolbox
When you open a SSIS project in Visual Studio/Bids, right click the toolbox and choose "Choose Items...". After this Visual Studio will be busy for a couple of seconds!

Right click toolbox

When the Choose Toolbox Items window appears, go to the SSIS Control Flow Items and search for the newly installed File-in-use Task and select it. Click ok to finish.

Choose Toolbox Items

Now the new task will appear in the toolbox. Ready to use! Have fun.

New task added

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