0

I am trying to download multiple files that are located in multiple folders from an FTP site into multiple folders on a specific drive using FTP task and script task in SSIS. I am not downloading the files into one specific folder. I have looked everywhere and I could not find any good information. Any help is much appreciated. Thanks.

Hmen
  • 41
  • 6
  • And what are the links between the files and folders? Do they share some naming part? Or are the destination folders the same name as the source folders for each file? Need a good bit more information. – Aaron Dietz May 31 '17 at 18:00
  • Possible duplicate of [Download files from multiple FTP folders in SSIS](https://stackoverflow.com/questions/20110156/download-files-from-multiple-ftp-folders-in-ssis) – Tab Alleman May 31 '17 at 18:58
  • The folders and the file share the same prefix. For instance, abc_20170531.txt would go to abc folder. I have tried using source path parameters but it only applies to one specific prefix. Thanks – Hmen May 31 '17 at 19:09
  • Tab Alleman, I will have more than 30 folders so I am trying to see if there is a more efficient way of downloading them before creating 30 foreach loop for each folder. Thanks for the help. – Hmen May 31 '17 at 19:51
  • @Hmen Are you grabbing all files in all folders off the source FTP? Meaning.. is there an umbrella folder that contains all the subfolders that you want to grab from, and you can grab everything under that umbrella folder without filter? – Aaron Dietz May 31 '17 at 20:56

1 Answers1

0

If you're not required to use a script task for some reason, then here is what you want to do:

  1. Create a Foreach Loop Container
  2. Under the Collection tab, Folder field, browse to the folder that contains all of the subfolders that you are pulling files from off your source FTP. (This is making the assumption that such a folder exists, which hopefully it does or you can create one)
  3. Check the Traverse Subfolders box, and choose Fully Qualified
  4. Under the Variable Mappings tab, create a variable @FileNameWithPath or whatever you want to call it. Index default is fine.
  5. Create a package variable called FileName
  6. Set the expression of FileName to this, which will remove the path:

RIGHT(@[User::FileNameWithPath], FINDSTRING(REVERSE(@[User::FileNameWithPath]), "\\", 1 )-1)

  1. Create a package variable called FilePrefix
  2. Make an expression to pull the prefix out of the FileName. It will be this, if you're grabbing everything before the first underscore:

LEFT( @[User::FileName],FINDSTRING(((DT_WSTR, 150) @[User::FileName] ), "_", 1)-1)

  1. Create another variable called DestinationFolder.
  2. Use the FilePrefix variable you just created to build the expression for DestinationFolder. It will be this:

"\\Your destination FTP\" + @[User::FilePrefix]

  1. Within your Foreach Loop Container, add a File System Task
  2. Set the File System Task to a move or copy, whatever you need, with a Source Variable of FileNameWithPath, and a destination variable of DestinationFolder
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • Thank you for the info but is there a way for the foreach loop to connect to the FTP server? How do you make the foreach loop to connect to FTP remote path? I did not know that was possible. Thanks! – Hmen Jun 02 '17 at 02:41
  • Sure thing, happy to help - to connect to the FTP server, you should only need to put the full path into step 2 above. If it require special credentials, you may also need to map the FTP as a network drive on the server running SSIS. @Hmen – Aaron Dietz Jun 02 '17 at 13:43
  • Thank you! I will try mapping it as a next work drive and get back to you if I have any questions. – Hmen Jun 02 '17 at 14:05