▼

Thursday, 19 January 2012

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.

59 comments:

  1. Arthur19 January 2012 at 22:53

    Awesome. On my to do list to try out.
    Thinking must be placed on Codeplex in http://ssisctc.codeplex.com/.

    ReplyDelete
    Replies
    1. Joost van Rossum20 January 2012 at 21:33

      Thanks! Let me know if there are any bugs or missing features. Will do a link suggestion at http://ssisctc.codeplex.com/.

      Delete
  2. Anonymous17 February 2012 at 17:32

    I'm trying to recreate your original package (copy / paste of source code) and i works like a charm, but I get an error when I get to:

    Dts.Variables["FtpFileList"].Value = fileNamesArray;

    ex.Message = "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.\r\n"



    I'm sure it's my fault, but do you have any idea as to what I might be missing?

    Regards

    Peter

    ReplyDelete
    Replies
    1. Joost van Rossum17 February 2012 at 19:18

      I think you are referring to the Foreach FTP File 'Enumerator' with a Script Task?

      Are there any files in the FTP folder (WorkingFolder correct)? You should probably check if it's value is NULL or the length of the array is 0.

      if (fileNames != null)
      {

      }

      You should also check if the datatype of the variable is correct. Please let me know if there are still some problems.

      Delete
  3. Anonymous3 May 2012 at 02:11

    I was enthused to see this had been created. But so far I've been unable to get it to work. As a test I created a Foreach Loop Container and selected the Foreach FTP File Enumerator. I selected the FTP Connection Manager, set the Ftp Working Directory to / and the Filter to *.*. The FTP location had 27 files so I expected the loop to quickly iterate through the list. When selecting Execute Container from the context menu the screen blinks as if it is getting ready to process...then nothing. It seems stuck in Debug...no yellow...no green...no red...it just sits there. When I check the execution results it tells me Validation started and completed and that it started and finished with 00:00:00.000 elapsed time. I have to go to the main Debug menue to break it out of debug. Any ideas what I need to do to get this to work?

    ReplyDelete
    Replies
    1. Joost van Rossum4 May 2012 at 09:11

      @David: Thanks for your feedback. New release should solve your problem.

      Delete
  4. Joost van Rossum4 May 2012 at 08:34

    There is a new version that solved two bugs:
    - some 64bit issues;
    - empty folder issue.

    Thanks all for the feedback and bug report!

    ReplyDelete
  5. EugeneD10 May 2012 at 22:47

    This is great and works if on one level of subfolders, what if i need to drill-down further in ftp structure to get the file? Root folder contains follder and in these folder there subfolder with folder that contain files, e.g.

    Root
    Folder1
    Folder1a
    File1
    Folder1b
    File2
    Folder2
    Folder2a
    File3
    Folder2b
    File4


    Thank you in advance

    ReplyDelete
    Replies
    1. Joost van Rossum11 May 2012 at 08:50

      Hi EugeneD,

      Please explain because, the traverse folder option goes multiple levels deep:
      /Root/file1.csv
      /Root/folder/file2.csv
      /Root/folder/subfolder/file3.csv

      Delete
    2. EugeneD15 May 2012 at 18:28

      Hi Joost,

      Thank you for replying to my post.

      Travers option only goes one level deep, so if I have files in a subfolder and I specify path to root only, it does not traverse to the lowest levels. I have a work around for this allready, to use another Foreach loop enumerating folders and passing the value to the Foreach FTP enumerator.

      Thanks,
      Eugene

      Delete
    3. Joost van Rossum15 May 2012 at 20:34

      Hi Eugene,

      Thx for your reply. It realy should work for multiple levels. I will recreate your folder structure and test it. Maybe it doesn't 'like' folders without files. Will let you know...

      Delete
  6. CT19 June 2012 at 22:46

    How should I define the FTP Task if using the "foreach ftp file enumerator"

    ReplyDelete
    Replies
    1. Joost van Rossum20 June 2012 at 08:14

      Hi CT,

      The variable that is filled by the Foreach Loop, contains the ftp file path (/data/myfile1.csv) of one of your ftp files. You can use that variable als a source variable in your FTP Task. Set IsRemotePathVariable to true so you can use the variable in the RemoteVariable property. Let me know whether you succeed...

      Delete
    2. Jim25 November 2013 at 19:12

      Hi Joost,
      I am having the same difficulty as CT w/ this step. (An example ssis package that users could download would go a long ways in explaining this). So my question is... what is the variable that is filled by the Foreach Loop (I assume this is something I have to create and setup to be filled through the "Variable Mappings" setting... but I can't get it to work). I know I'm very close but just can't get over the hump. Obviously... after that (as you explained)... I should just use that variable for the Remote Variable.

      Delete
    3. Joost van Rossum26 November 2013 at 09:37

      Hi Jim,

      It works exactly the same as a regular File Enumerator. On the Collection page you need to select your FTP connection and set the path and filter.

      On the Variable Mapping page you need to add one string variable and set the index to 0.

      If you now execute the Foreach Loop. Then it will fill the string variable with the filepath of a FTP file. You can use that path in a FTP Task or for testing purposes you could add a Script Task with a messagebox or fireevent. See this example.

      Delete
    4. Jim26 November 2013 at 14:05

      Thank you Joost. The msg window displaying the variable mapping at run time resolved my issue. Turns out on the variable I was using to set the ftp path, I wasn't using a trailing "/".... so instead if getting this "/WorkingDir/Filename.csv"... I was getting this "/WorkingDirFilename.csv" which was causing anything I used the variable mapping for to fail.

      Delete
    5. Joost van Rossum27 November 2013 at 21:36

      Hi Jim,

      Good to hear it works. I have included a little example video in the post.

      Delete
  7. CT27 June 2012 at 17:28

    I get the following error:
    Error: 0xC0010018 at {DD53BD69-43FE-498B-B850-FA21CD10E197}: Error loading value "
    {6635D433-0693-4EDD-9C58-3BB1D2395294}
    {6635D433-0693-4EDD-9C58-3BB1D2395294}
    <DTS:Prope" from node "DTS:ForEachEnumerator".
    SSIS package "FTP For Each FTP File.dtsx" starting.
    SSIS package "FTP For Each FTP File.dtsx" finished: Success.

    Any ideas why it cannot load: "Error loading value "<DTS:ForEachEnumerator"? I installed the "Foreach FTP File Enumerator - SSIS 2008 download" and was able to select "Foreach Ftp File Enumerator" from the Foreach Loop Editor.

    ReplyDelete
    Replies
    1. Joost van Rossum30 June 2012 at 23:15

      Hi CT,

      Is it a validation error or a runtime error? Where is GUID 6635D433-0693-4EDD-9C58-3BB1D2395294 refering to (view code of package)? Please contact me via this form for easy communication...

      Kind Regards,

      Joost

      Delete
  8. Anonymous5 July 2012 at 22:59

    Is the linked files to the latest versions? I keep getting the following error.

    Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004003 "Invalid pointer". This occurs when the ForEach Enumerator cannot enumerate.

    ReplyDelete
    Replies
    1. Joost van Rossum5 July 2012 at 23:08

      @anonymous: Yes it is the latest version. Please contact me via this webform to provide more details (like ssis version and details about the ftp folder).

      Kind regards,

      Joost

      Delete
  9. Stucifer10 July 2012 at 19:36

    Hi Joost, We are using the enumerator with SQL 2008 and it works very well. We are upgrading to SQL 2012. If we us the newer 2012 version of the ForEach loop enumerator can it be installed on the same machine as the 2008 version? Should I uninstall the 2008 ForEach enumerator first? Are there any known issues? Thanks.

    ReplyDelete
    Replies
    1. Joost van Rossum10 July 2012 at 20:11

      Both version can be installed side by side. Are you developing new packages or upgrading 2008 packages to 2012?

      Delete
  10. Stucifer10 July 2012 at 20:40

    Hi. I just tried the upgrade after first installing the newer version. Both on the same dev machine. It complained about the control. Gave an error. I'm trying to see if your control appears in the toolbox and it doesn't seem to. So I'll look into that. Here's the error from the upgrade wizard:
    * Error 0xc0010018: Foreach Loop Container: Error loading value "<DTS:ForEachEnumerator xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ObjectName="{FA265B05-C3CA-4BA1-84F9-8E55010DD491}" DTS:DTSID="{FA265B05-C3CA-4BA1-84F9-8E55010DD491}" DTS:Description="" DTS:CreationName="ilionx.SSIS.Enumerators.SortedFile, ilionx.SS" from node "DTS:ForEachEnumerator".

    ReplyDelete
    Replies
    1. Joost van Rossum10 July 2012 at 20:44

      upgrading custom components is a pain in the ***. Workaround for now: just change the foreach loop to a standard enumerator before upgrading and changing it back again after upgrading.

      Delete
  11. Unknown19 July 2012 at 22:03

    There's any usable expression to get count of iterated items?

    ReplyDelete
    Replies
    1. Joost van Rossum20 July 2012 at 09:26

      The Foreach Folder Container doesn't support that. I have added a property "FireInformationMessages" that will fire an information event with the number of files.
      An other option is to add a simple Script Task within the loop that raises a value of a variable. Only one row of code. Something like this psuedo code: Dts.Variables["intCounterVar"].Value = Dts.Variables["intCounterVar"].Value + 1;

      Delete
  12. Anonymous18 September 2012 at 07:53

    Hi Joost,
    How can I attach the file as attachement to send mail task which is in the foreach loop???
    Please help me

    ReplyDelete
    Replies
    1. Joost van Rossum18 September 2012 at 09:59

      Hi Anonymous,

      I think you need an expression to set the attachment property dynamically. You can use the variable from the foreach loop in that expression. Let me know if you succeeded.

      Regards,

      Joost

      Delete
  13. Anonymous1 November 2012 at 22:28

    * MOVED FROM FILE ENUMERATOR TO FTP ENUMERATOR *

    Great control. very useful. One point. I have just had the control fail when there are files in the root folder of the ftp path. Any idea why that might be ? If I remove the files from the root it work perfectly

    ReplyDelete
    Replies
    1. Joost van Rossum1 November 2012 at 22:28

      Hi Anonymous,

      I don't have that problem, but please post the error. Maybe that will clarify to cause.

      Delete
  14. Unknown8 November 2012 at 19:28

    This is just what I was looking for. Thank you for creating and posting this.

    Is it possible that the Sort behavior is reversed? When I choose Asc I get the letters way down the alphabet first - "Y" files in the folder that I am accessing. When I choose Desc I get the "A" files first.

    Mark

    ReplyDelete
    Replies
    1. Joost van Rossum8 November 2012 at 22:53

      Hi Mark,

      Thanks for reporting the bug. I found it and solved it... Will do some more testing and upload a new version this weekend.

      Regards,

      Joost

      Delete
  15. Arun kumar20 December 2012 at 04:12

    Hi,

    We are using Forach FTP File enumeration task, but we are facing issues while traverse it to subfolder.

    We have Foreach FTP File Enumeration and an FTP Task inside the it.

    We have configure the things mentioned above, and facing the issue in FTP task as "file name doesnt start with /". Can you please help on this

    ReplyDelete
    Replies
    1. Joost van Rossum20 December 2012 at 08:40

      Is your variable mapping OK in the foreach loop. If so try the following: Add a Script Task inside the loop. Add the variable from your foreach loop as readonly variable In the Script Task. Choose a script language (VB.Net or C#). Click on Edit Script and add the following row to th main method
      C# variant:
      MessageBox.Show(Dts.Variables["User::YourVarriable"].Value.ToString());
      Or the VB.Net variant:
      MessageBox.Show(Dts.Variables("User::YourVariable").Value.ToString())
      And then run the package to see what's the value of the variable inside the foreach loop.

      Delete
  16. Anonymous7 August 2013 at 02:39

    Hello,
    just downloaded the FTP enumerator but it deos not register and show up in the list of tools. Can you help ?
    thanks
    Jose

    ReplyDelete
    Replies
    1. Joost van Rossum7 August 2013 at 10:25

      Make sure you have restarted BIDS/Visual Studio after installation. See Installation part above.

      Delete
  17. Unknown3 September 2013 at 19:46

    Hi Joost,
    Same problem here... I'm back with this component after a year (and another job) but it doesn't register properly in Win 7 64bit Edition. There's additional steps for installation?

    ReplyDelete
    Replies
    1. Joost van Rossum6 September 2013 at 10:13

      Win 7 64bit should be no problem... I developed it on Win 7 64bit. Do you run the installer as administrator? It needs to copy the dll's to C:\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators\ and it registers them into the GAC.

      Delete
  18. Unknown3 September 2013 at 21:56

    I get this error message in Win7 x64

    Error 1 Error loading APL.LeerBajaPSAplicateca.dtsx: Error loading value " {E30479B8-090D-406B-A095-402B4CBD9809} {E30479B8-090D-406B-A095-402B4CBD9809} <DTS:Prope" from node "DTS:ForEachEnumerator".

    ReplyDelete
    Replies
    1. Joost van Rossum6 September 2013 at 10:16

      Do you see the two dll's in the GAC and in C:\Program Files\Microsoft SQL Server\100\?DTS\ForEachEnumerators\
      The filename starts with ilionx.SSIS.enumerators.*******

      Delete
  19. Unknown21 January 2014 at 03:13

    Hello, the transverse subfolders option is causing the package to get stuck. I don't have any log because it just hangs

    ReplyDelete
    Replies
    1. Joost van Rossum21 January 2014 at 07:08

      Hello wzfqn3,

      If the number of files is very large or the connection very slow, then it could take a while! There is a FireInformation Property. You could set that to true. It gives some logging.

      Regards,

      Joost

      Delete
  20. Unknown20 February 2014 at 15:26

    Hi,

    This is great - thanks very much

    ReplyDelete
  21. Unknown3 March 2014 at 11:43

    Hi,

    I’ve been trying to download the file TEST-FILE-20140301-Test.ZIP using the wildcard . The date of the file has to be 2 days ago so todays file would be TEST-FILE-20140301-Test.ZIP. The filename always stays the same.
    I’ve tried this and it does not work "TEST-FILE-" & Year(DateAdd("d", -2, Now)) & Right("0" & Month(DateAdd("d", -2, Now)), 2) & Right("0" & Day(DateAdd("d", -2, Now)), 2) & "-TEST.ZIP"

    Many thanks,

    Zee

    ReplyDelete
    Replies
    1. Joost van Rossum7 March 2014 at 08:12

      You don't need the Foreach FTP Enumerator if you only want to download 1 specific file.
      Use your expression (add casts in it) with the ftp task. Or go to the SSIS MSDN forum.

      Delete
  22. Unknown25 March 2014 at 18:14

    Joost,

    A great addition to the loop container.

    Can I pass a variable to Ftp Working Directory? Then I could bring in the base folder into the package.

    Thanks, Stephen

    ReplyDelete
    Replies
    1. Joost van Rossum25 March 2014 at 19:54

      Stephen,

      You can use expressions in the Collection pane/tab to override hardcoded values with variables.

      Regards,

      Joost

      Delete
  23. rever21 January 2015 at 08:13

    Hello.
    Sometimes I get error while execute Foreach FTP File enumerator:

    Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0xC001602A "An error occurred in the requested FTP operation. Detailed error description: %1". This occurs when the ForEach Enumerator cannot enumerate.

    FTP server at this time is online and ready to connect.
    I don't have any ideas to fix this error.
    Can you help me?

    ReplyDelete
    Replies
    1. Joost van Rossum3 April 2015 at 14:26

      This only happens if it cannot connect to the FTP Server or cannot reach the FTP Working Directory. Can you use the exact same connection information and working folder in an FTP Client to connect?

      Delete
    2. Unknown19 May 2017 at 05:31

      Hi,

      I can connect using the same credentials in FileZilla but still getting this error. Please help.

      Delete
  24. Kenneth11 February 2016 at 15:24

    Hi there, SSIS package running fine with taking all files from a ftp dir to a local dir. I´m having some issues with automating the SSIS package from Management studio. And the error seems to be about this plugin:

    Message
    Executed as user: NT Service\SQLAgent$PORTRAIT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 14:59:37 Error: 2016-02-11 14:59:37.90 Code: 0xC0010018 Source: FTP All files over Description: Error loading value "<DTS:PrecedenceConstraint DTS:refId="Package.PrecedenceConstraints[Constraint]" DTS:CreationName="" DTS:DTSID="{37D1E658-BED3-4042-8853-AC4DC4B6939B}" DTS:From="{FB8D3A07-5009-4977-983C" from node "DTS:PrecedenceConstraints". End Error Could not load package "\\Aosvxd4e015\SSIS\SOS_FRnow_Import\SOS_FRnow_Import\bin\Development\SOS-FRnow-Import.dtsx" because of error 0xC0010014. Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. Source: SOS-FRnow-Import Started: 14:59:37 Finished: 14:59:37 Elapsed: 0.796 seconds. The package could not be loaded. The step failed.

    Any ideas what this can be about?

    ReplyDelete
    Replies
    1. Joost van Rossum21 March 2016 at 13:48

      Have you installed the custom enumerator on the database (SSIS) server as well?

      Delete
  25. Unknown17 February 2017 at 11:26

    Hi,

    What am I doing wrong? I am using SQL SSIS 2016 and run the setup. I can see the dll's in C:\Program Files\Microsoft SQL Server\130\DTS\ForEachEnumerators, i restart Visual Studio but when I add a new For Each Loop I cannot see the FTP File Enumerator?

    ReplyDelete
    Replies
    1. Joost van Rossum17 February 2017 at 11:28

      Make sure to install the 32bit version for Visual Studio

      Delete
  26. Unknown17 February 2017 at 14:50

    Perfect, got it, I had 64bit installed - my mistake, thanks

    ReplyDelete
    Replies
    1. Joost van Rossum17 February 2017 at 16:38

      No problem. Need to find an installer that installs both 32 en 64bit at the same time...

      Delete
  27. Ania14 July 2020 at 13:58

    Is there a version for SSIS 2017?

    ReplyDelete
  28. gratis progjes! by benepc11 January 2021 at 10:19

    Hi, Is there an 2019 version?
    Regards

    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.