How to Move file from One Folder to Another Folder on FTP Server by using SSIS Package -SQL Server Integration Services(SSIS) Tutorial

Scenario: 

Vendor put the file with date in one of the folder for us to download. After downloading the file we want to move those files to the Archive Folder that exists on FTP Server.

Solution: 

To handle this situation, we will be using the Script Task in SSIS Package as FTP Task capabilities are limited to perform this task.

We will learn 
  1. How to Create an SSIS Package from scratch
  2. How to Create Variables to save FTP Server, Remote Folder , Local Folder Paths
  3. How to use variables in Script Task in SSIS Package
  4. How to Download the file by using Script Task and move to different folder ( Archive) on FTP Server
Expressions used in the SSIS Package
VarFileName = 

@[User::FileName]+Replace(Substring((DT_WSTR,30)GETDATE(),1,10),"-","")+".txt"

ArchFilePath=
"../Archive/"+ @[User::FileName] +Replace(Substring((DT_WSTR,30)GETDATE(),1,10),"-","")+".txt"


Script used in Script Task to Move file from one folder to another folder on FTP Server

       public void Main()
        {

string UserName;
string Password;
string LocalFolder;
string FileName;
string FTPFileFullPath;
string ArchFilePath;


UserName = Dts.Variables["User::UserName"].Value.ToString();
Password = Dts.Variables["User::Password"].Value.ToString();
LocalFolder = Dts.Variables["User::LocalFolder"].Value.ToString();
FTPFileFullPath = Dts.Variables["User::FTPServer"].Value.ToString()
+ Dts.Variables["User::RemoteFolder"].Value.ToString()
+ Dts.Variables["User::VarFileName"].Value.ToString();
FileName = Dts.Variables["User::VarFileName"].Value.ToString();
ArchFilePath=Dts.Variables["User::ArchFilePath"].Value.ToString();


//Download the file to local folder
WebClient Webrequest = new WebClient();
Webrequest.Credentials = new NetworkCredential(UserName, Password);
MessageBox.Show(FTPFileFullPath);
byte[] newFileData = Webrequest.DownloadData(FTPFileFullPath);
File.WriteAllBytes(LocalFolder+FileName, newFileData);


//Move the File to Archive Folder
FtpWebRequest ftpRequest = (FtpWebRequest)WebRequest.Create(FTPFileFullPath);
ftpRequest.Credentials = new NetworkCredential(UserName, Password);
ftpRequest.Method = WebRequestMethods.Ftp.Rename;
ftpRequest.RenameTo = ArchFilePath;
FtpWebResponse ftpResp = (FtpWebResponse)ftpRequest.GetResponse();

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;
        }

Move file from One folder to Another Folder on FTP Server in Script Task in SSIS Package



Related Posts / Videos on FTP Task / Script Task