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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply