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

4 comments:

  1. hello i'm using sql server 2012 and I have .net and c# 2010. m using your script from your blogs its showing error any sugessation why its showing error hopping to hear soon

    ReplyDelete
  2. Please your script and application turn off you can to send me the link for your package brother

    ReplyDelete
  3. Thanks for sharing your knowledge to install & crack the Time Tables, but you need to update
    it now. because there is a 2022 version available now.
    key4pc.com
    folder-lock-crack

    ReplyDelete
  4. Is this a paid topic or do you change it yourself?
    However, stopping by with great quality writing, it's hard to see any good blog today.
    http://licensedinfo.com/
    Folder Lock Crack
    NextUp TextAloud Crack
    Active File Recovery crack
    Symantec Endpoint Protection crack
    ChrisPC VideoTube Downloader Pro'

    ReplyDelete