Transfer Multiple Files from or to FTP remote path to local path - SSIS

This is a common mistake which SSIS developer's thinks that they need Foreach Loop for transfer multiple files from ftp to local or from local to ftp.
I had a post about using masks in FTP task for transfer multiple files, but it seems that I should clarify this more than before.

Note that you don't need any Loop structure for transfer multiple files from remote path to local or from local path to remote. you just need to use MASK.
I explain in details with this scenario:
you have a remote ftp directory which you want to transfer all files in this folder to a local folder ( from FTP address to local) . and then copy all files from this local folder to another remote path ( from local to FTP address ).

Solution:

you have two steps:
1- receive files from remote ftp address:
add a FTP task, set ftp connection, then go to file transfer tab,
set Operation as "Receive files"
then set localPath to an existing folder as you want, for example:
D:\MyTempFolder
then in RemotePath set address to remote directory as you want, BUT NOTE THAT YOU SHOULD ADD MASK AFTER IT.
for example like this:
/myftpurl.com/wwwroot/myRemoteFolder/*.*
note that "*.*" means that you want to receive all files from this remote directory.

mid step: create new variable of string datatype in package scope, let's name it as LocalPath
and set it with your local address but with MASK, for example in our case it will be:
D:\MyTempFolder\*.*

2- send files from local directory to remote ftp address:
add another ftp task, set ftp connection, then in file transfer tab,
set Operation as "Send files"
set IsLocalPathVariable as True
then set LocalVariable with User::LocalPath
( the reason of using variable as localpath is that you can not set localpath directly with MASK, SSIS will raise compilation error. but if you put a variable there and set mask in variable value, SSIS will try to solve it at run time and everything will work fine)
then set remotePath to the remote folder which is your destination, you don't need to add mask here, for example it can be:
/mysecondftpurl.com/wwwroot/DestinationFolder

That's all. execute it now.

Posted by Reza Rad on Thursday, October 14. 2010 at 23:49 in SSIS

623141 hits

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Sonnie says,

Wednesday, December 21. 2011 at 05:15

Hey Reza, The method you described for sending files to FTP site is not working for me. I can download mutiple files using the method i.e masking *.* or *.txt or *.csv but this does not work when you are uploading files. Any suggestion or workaround? Thanks Sonnie

Reza Rad says,

Friday, December 23. 2011 at 01:11 (Link)

Hi Sonnie, could you let me know what is your scenario exactly? what you want to accomplish? and I will help you in implementation. shortly there shouldn't be problem in uploading multiple files. Regards, Reza

jampa says,

Wednesday, March 14. 2012 at 07:05

Hi Reza, I need to get the files from a folder which changes daily , with the date, todays folder is folder03122012 and tomorrow would be folder03122012. so as this changes dynamically how do get files from this folder.

Reza Rad says,

Thursday, March 15. 2012 at 17:24 (Link)

Hi Jampa, By the folder you mean remote folder? if yes, you can set remote folder path dynamically with variable so simply you need to make the variable with expressions. and then use it in FTP Task. Now let me know where exactly you need a help? in making the expression or in FTP configuration? Regards, Reza

jampa says,

Wednesday, March 14. 2012 at 13:44

[FTP Task] Error: An error occurred with the following error message: "Changing current directory to "\user\xx\03142012". 550 Folder /user/xx/03142012 not found . ". I tried your approach and i got this error message can you let me know what im doing wrong here

Reza Rad says,

Thursday, March 15. 2012 at 17:19 (Link)

Hi Jampa, Do you have access to FTP? when you browsed to the ftp web site could you see folders there? I think this is a permission issue Regards, Reza

Jeff says,

Saturday, March 24. 2012 at 03:46

Hi Reza, Could you throw more light on how I would download files from different folders in an ftp. Eg. /download/CSK/file5432.csv, /download/JMK/file1832.csv and /download/JKJ/file5002.xls Thx!

Reza Rad says,

Sunday, March 25. 2012 at 12:38 (Link)

Hi Jeff, you need to use a have the list of folders you want to get files from, then you can load the list of folders in an Object type variable with a RecordSet Destination within a data flow task. and after that you can use a Foreach Loop Container to loops through each folder, and using expressions change the source variable in FTP Task. There are some details through implementing this, so let me know where you need more details. Regards, Reza

Jeff says,

Wednesday, July 18. 2012 at 04:05

Hi Reza, Is it possible to publish an article on downloading multiple files from different ftp folders? I can hear many cries on blogs with no answers

Reza Rad says,

Thursday, July 19. 2012 at 04:46 (Link)

Hi Jeff, You can use Foreach FTP Enumerator from here: http://microsoft-ssis.blogspot.co.nz/2012/01/custom-ssis-component-foreach-ftp-file.html Regards, Reza

Roy says,

Wednesday, August 15. 2012 at 08:50 (Link)

Thanks -- this was very helpful. To send multiple files, I set IsLocalPathVariable to True, opened the LocalVariable box and set Name to LocalPath. I then switched to Explorer to navigate to my source folder, then copied the entire address from the address bar, and added *.*, as in: \\Snickers\TDWI\Sourcefiles\*.* for the Value box. Voila. It worked. No looping needed. Also, it did not try to upload any files from the subfolders, which was good. Thanks!

ahd261 says,

Thursday, November 14. 2013 at 09:24

Super but how ca we do when we want to transfer a folder including a subfolder including a sub subfolder... to a remote path ?

Reza Rad says,

Tuesday, November 26. 2013 at 11:29 (Link)

The best way to do that is writing .NET scripts to read the exact folder structure from your local and create that structure in the remote, or reverse Regards, Reza

Prudvi says,

Thursday, November 21. 2013 at 02:45

Hi All, I have a doubt about FTP task.I have read all the comments but those are not helping my conditions.We have one common FTP task all the jobs and each job is extracting one xml file sending those through the FTP task(Variable is var_LocalPath).Now I have doveloped one job where it will generate three files.Now I want to FTP those files using that same FTP task.I have used varialbles(var_LocalPath_XYZ,var_LocalPath_ABC,var_LocalPath_IJK)like this to avoid duplicate variales.Now i am facing challenge during FTP.If I decalre var_LocalPath I am able to transfer only one file.Can anyone please suggest me here

Reza Rad says,

Tuesday, November 26. 2013 at 11:11 (Link)

Hi Prudvi, My question is why you use only a single FTP task for all operations? if that's because of consistent configuration, you can make configurations consistent with parameters or package configurations. otherwise using a single task for multiple operations is possible but will increase complexity and reduce readability and it is not recommended. Regards, Reza

Add Comment

Name
Email
Homepage
In reply to
Comment: Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

Enter the string from the spam-prevention image above:
: Remember Information?
Subscribe to this entry
Submitted comments will be subject to moderation before being displayed.

Transfer Multiple Files from or to FTP remote path to ...

Reza Rad's Technical blog

Publications

Web Casts

Certifications

Search

Recent Entries

Links

Popular Entries

Awards

Speaking Sessions and Presentations

Categories

Archives

Calendar

Syndicate This Blog

Statistics

Blog Administration

Powered by

Twitter

Transfer Multiple Files from or to FTP remote path to local path - SSIS

	June '18
Mo	Tu	We	Th	Fr	Sa	Su
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30