How to Download a File and Then Mark as Downloaded on FTP Server by SSIS Package - SQL Server Integration Services(SSIS) Tutorial

Scenario:

We download file for each of the day from FTP Task. Once the file is successfully downloaded to the local folder we want to mark that file as downloaded, By doing that our vendor knows that that we have downloaded the file and also we can always take  a look which files we have downloaded.

Solution:

We will be using Script Task to handle this requirement. We will download the file and after that add _Downloaded at the end of file.

What we will learn in this video
  1. We will learn how to Create an SSIS Package
  2. How to Create Package Parameters for FTP Server, User Name, Password, Remote Path and Local Folder
  3. How to use Package Parameters in SSIS Package
  4. How to download file from FTP Server to Local Folder by using Script Task in SSIS Package
  5. How to Rename File on FTP Server by using Script Task in SSIS Package

Expression used to set the value of VarFileName variable

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


Script to use in Script Task in SSIS Package to Download and Mark as Downloaded on FTP Server


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


UserName = Dts.Variables["$Package::UserName"].Value.ToString();
Password = Dts.Variables["$Package::Password"].Value.ToString();
LocalFolder = Dts.Variables["$Package::LocalFolder"].Value.ToString();
FTPFileFullPath = Dts.Variables["$Package::FTPServer"].Value.ToString()
+ Dts.Variables["$Package::RemoteFolder"].Value.ToString()
+ Dts.Variables["User::VarFileName"].Value.ToString();
FileName = Dts.Variables["User::VarFileName"].Value.ToString();
//# move file to remote server


//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);


//Rename the file to downloaded
FtpWebRequest ftpRequest = (FtpWebRequest)WebRequest.Create(FTPFileFullPath);
ftpRequest.Credentials = new NetworkCredential(UserName, Password);
ftpRequest.Method = WebRequestMethods.Ftp.Rename;
ftpRequest.RenameTo = FileName.Replace(".","_Downloaded.");
FtpWebResponse ftpResp = (FtpWebResponse)ftpRequest.GetResponse();


How to Rename a File on FTP Server by using Script Task in SSIS Package



Related Posts / Videos on FTP Task / Script Task

1 comment:

  1. This actually is a very useful and informative blog post about SSIS and I guess every aspect is covered meticulously.

    SSIS Upsert

    ReplyDelete