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.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.