 |
| 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.
Awesome. On my to do list to try out.
ReplyDeleteThinking must be placed on Codeplex in http://ssisctc.codeplex.com/.
Thanks! Let me know if there are any bugs or missing features. Will do a link suggestion at http://ssisctc.codeplex.com/.
DeleteI'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:
ReplyDeleteDts.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
I think you are referring to the Foreach FTP File 'Enumerator' with a Script Task?
DeleteAre 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.
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@David: Thanks for your feedback. New release should solve your problem.
DeleteThere is a new version that solved two bugs:
ReplyDelete- some 64bit issues;
- empty folder issue.
Thanks all for the feedback and bug report!
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.
ReplyDeleteRoot
Folder1
Folder1a
File1
Folder1b
File2
Folder2
Folder2a
File3
Folder2b
File4
Thank you in advance
Hi EugeneD,
DeletePlease explain because, the traverse folder option goes multiple levels deep:
/Root/file1.csv
/Root/folder/file2.csv
/Root/folder/subfolder/file3.csv
Hi Joost,
DeleteThank 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
Hi Eugene,
DeleteThx 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...
How should I define the FTP Task if using the "foreach ftp file enumerator"
ReplyDeleteHi CT,
DeleteThe 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...
Hi Joost,
DeleteI 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.
Hi Jim,
DeleteIt 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.
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.
DeleteHi Jim,
DeleteGood to hear it works. I have included a little example video in the post.
I get the following error:
ReplyDeleteError: 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.
Hi CT,
DeleteIs 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
Is the linked files to the latest versions? I keep getting the following error.
ReplyDeleteError: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004003 "Invalid pointer". This occurs when the ForEach Enumerator cannot enumerate.
@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).
DeleteKind regards,
Joost
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.
ReplyDeleteBoth version can be installed side by side. Are you developing new packages or upgrading 2008 packages to 2012?
DeleteHi. 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:
ReplyDelete* 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".
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.
DeleteThere's any usable expression to get count of iterated items?
ReplyDeleteThe Foreach Folder Container doesn't support that. I have added a property "FireInformationMessages" that will fire an information event with the number of files.
DeleteAn 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;
Hi Joost,
ReplyDeleteHow can I attach the file as attachement to send mail task which is in the foreach loop???
Please help me
Hi Anonymous,
DeleteI 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
* MOVED FROM FILE ENUMERATOR TO FTP ENUMERATOR *
ReplyDeleteGreat 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
Hi Anonymous,
DeleteI don't have that problem, but please post the error. Maybe that will clarify to cause.
This is just what I was looking for. Thank you for creating and posting this.
ReplyDeleteIs 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
Hi Mark,
DeleteThanks for reporting the bug. I found it and solved it... Will do some more testing and upload a new version this weekend.
Regards,
Joost
Hi,
ReplyDeleteWe 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
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
DeleteC# 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.
Hello,
ReplyDeletejust downloaded the FTP enumerator but it deos not register and show up in the list of tools. Can you help ?
thanks
Jose
Make sure you have restarted BIDS/Visual Studio after installation. See Installation part above.
DeleteHi Joost,
ReplyDeleteSame 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?
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.
DeleteI get this error message in Win7 x64
ReplyDeleteError 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".
Do you see the two dll's in the GAC and in C:\Program Files\Microsoft SQL Server\100\?DTS\ForEachEnumerators\
DeleteThe filename starts with ilionx.SSIS.enumerators.*******
Hello, the transverse subfolders option is causing the package to get stuck. I don't have any log because it just hangs
ReplyDeleteHello wzfqn3,
DeleteIf 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
Hi,
ReplyDeleteThis is great - thanks very much
Hi,
ReplyDeleteI’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
You don't need the Foreach FTP Enumerator if you only want to download 1 specific file.
DeleteUse your expression (add casts in it) with the ftp task. Or go to the SSIS MSDN forum.
Joost,
ReplyDeleteA 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
Stephen,
DeleteYou can use expressions in the Collection pane/tab to override hardcoded values with variables.
Regards,
Joost
Hello.
ReplyDeleteSometimes 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?
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?
DeleteHi,
DeleteI can connect using the same credentials in FileZilla but still getting this error. Please help.
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:
ReplyDeleteMessage
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?
Have you installed the custom enumerator on the database (SSIS) server as well?
DeleteHi,
ReplyDeleteWhat 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?
Make sure to install the 32bit version for Visual Studio
DeletePerfect, got it, I had 64bit installed - my mistake, thanks
ReplyDeleteNo problem. Need to find an installer that installs both 32 en 64bit at the same time...
DeleteIs there a version for SSIS 2017?
ReplyDeleteHi, Is there an 2019 version?
ReplyDeleteRegards