Upload today's files from local directory to SFTP server

4
votes

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)

2
ssissftpfilezilla
You did not provide an operating system or what coding language you are using. It is like asking how much water does it take to fill a bucket – DEVPROCB
you should have rather ignored the question and respond rudely. who do you think you are? – ninying90
Actually I was interested in helping but had no way to provide an answer. Without more info you can't be helped. I was not trying to be rude just needed more info, but I will ignore it now, now that you mentioned it – DEVPROCB

2 Answers

5
votes

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:

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

(I'm the author of WinSCP)

0
votes

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;