Script Task : DownLoad File From FTP

The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides.

Following is the example written in C# to download file from FTP server and store it on local machine, though SSIS built in FTP Task provides this functionality but if we have our custom requirements manipulate that file before storing or ti get only specific files from server we can write our own.

public void Main()
  {
            try
            {
    ConnectionManager ftpcm = Dts.Connections["FTP Connection Manager"];
    FtpClientConnection ftpConn = new FtpClientConnection(ftpcm.AcquireConnection(null))
          
                ftpConn.Connect();
             
 // Set work folder with the value of the variable
                ftpConn.SetWorkingDirectory(Dts.Variables["FTPSource"].Value.ToString());

                String[] fileNames;
                String[] folderNames;
                ftpConn.GetListing(out folderNames, out fileNames);
                
                string selectedFileName=string.Empty;
                foreach (string file in fileNames)
                {

                   if(file.ToLower().Contains("readme"))
                   {
                    selectedFileName= file;
                    break;
                   }
                }

                if (!string.IsNullOrEmpty(selectedFileName))
                {
                    ftpConn.ReceiveFiles(fileNames, Dts.Variables["FTPDestination"].Value.ToString(), true, false);

                    // Close connection
                    ftpConn.Close();
                    Dts.TaskResult = (int)ScriptResults.Success;
                    return;
                    
                }
                else
                {
                   
                    // add code according to your requirments if file not found on ftp
                
                  
                }
            }

            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Script Task", "Error in processing file", string.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
                //Log Exception
            }

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

Reference: http://www.mssqltips.com/sqlservertip/1641/get-list-of-files-from-an-ftp-server-in-sql-server-integration-services/