Upload today's files from local directory to SFTP server

Question

Please, I need help loading only current day files from local directory to SFTP server. Apparently, FTP Task in SSIS cannot move to SFTP, only FTP.

Moreover, I have FileZilla. Can I use FileZilla in SSIS? or can I make FileZilla automatically to send the files at a specific time? (using Windows 10)

Answer 1

You cannot use FileZilla. FileZilla does not support any kind of scripting.

There are many other scriptable SFTP clients.

Your task is easy with WinSCP, as it has a syntax to select the today's files.

You can use a batch file like:

winscp.com /ini=nul /command ^
    "open sftp://username:password;[email protected]/" ^
    "put -filemask=*>=today ""c:\local\path\*"" ""/remote/path/""" ^
    "exit"

The >=today keyword is supported by WinSCP 5.15 and newer only.

In older versions, you can use %TIMESTAMP% syntax, particularly >=%%TIMESTAMP#yyyy-mm-dd%%, instead of >=today.

You can have WinSCP GUI generate the batch file template for you, including the host key fingerprint part.

References:

• Automating file transfers to SFTP server;
• Uploading the most recent file.

You can use the script in SSIS or schedule it with Windows scheduler.

(I'm the author of WinSCP)

Answer 2

You can use SSIS script task with Winscp to upload file on FTP using Winscp with the help of scheduling job of ssis Package

use the following code in Script task

string winscpPath = Dts.Variables["winSCPPath"].Value.ToString(); 
string username = Dts.Variables["ftpUsername"].Value.ToString(); 
string password = Dts.Variables["ftpPassword"].Value.ToString(); 
string ftpSite = Dts.Variables["ftpSite"].Value.ToString(); 
string localPath = Dts.Variables["localPath"].Value.ToString(); 
string remoteFTPDirectory = Dts.Variables["remoteFTPDirectory "].Value.ToString(); 
string sshKey = Dts.Variables["sshKey"].Value.ToString();
Boolean winSCPLog = (Boolean)Dts.Variables["winSCPLog"].Value;
string winSCPLogPath = Dts.Variables["winSCPLogPath"].Value.ToString();

SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = ftpSite,
UserName = username,
Password = password,
SshHostKeyFingerprint = sshKey
};

try
{
  using (Session session = new Session())
  {
    // WinSCP .NET assembly must be in GAC to be used with SSIS,
    // set path to WinSCP.exe explicitly, if using non-default path.
    session.ExecutablePath = winscpPath;
    session.DisableVersionCheck = true;

    if(winSCPLog)
    {
      session.SessionLogPath = @winSCPLogPath + @"WinscpSessionLog.txt";
      session.DebugLogPath = @winSCPLogPath + @"WinscpDebugLog.txt";
    }

    // Connect
    session.Timeout = new TimeSpan(0,2,0); // two minutes
    session.Open(sessionOptions);

    TransferOptions transferOptions = new TransferOptions();
    transferOptions.TransferMode = TransferMode.Binary;

    try
    {
      session.GetFiles(remoteFTPDirectory + "/" + 
      fileToDownload, localPath, false, transferOptions);
    }
    catch (Exception e)
    {
      Dts.Events.FireError(0, null,
      string.Format("Error when using WinSCP to download file: {0}", e), null, 0);
      Dts.TaskResult = (int)DTSExecResult.Failure;
    }
  }
}
Dts.TaskResult = (int)ScriptResults.Success;