Transferring file between two remote FTP and SFTP servers in SSIS/C# and archiving source files to another directory afterwards

Question

I've been struggling with this for a while and I do not really understand how to solve this situation by using SSIS (I was thinking about creating a Script task in C#)

This is my problem: everyday a file is created and stored in a certain directory in a specific server (I can access it by using a FTP protocol within a client-server application such as FileZilla or any other program). I want to transfer that file to a different server that uses SFTP protocol. And then, once it has been successfully placed in that server move the original file to a different folder on the source FTP server.

Take a look at this example:

My file is stored in this directory on the source FTP server:

I need to transfer the file myfiletest.csv to different SFTP server. The path is: SecondServer/placehere.

Finally, once the file was transferred, I want to move the source file to a folder Process.

Been trying to copy and move the file from the first server to the second one without success and I'm kinda lost right now.

Can help me to solve it?

This is how I'm trying to read the files from the FTP server (i've not managed to read it properly or even save it in my local environment).

 public void Main()
 {
    var directory =
        new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());

    FileInfo[] files = directory.GetFiles();
    DateTime lastModified = DateTime.MinValue;

    foreach (FileInfo file in files)
    {
        if (file.LastWriteTime > lastModified)
        {
            lastModified = file.LastWriteTime;
            Dts.Variables["User::FileName"].Value = file.ToString();
        }
    }

     MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());


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

Martin Prikryl Martin Prikryl · Accepted Answer · 2021-04-02T15:32:26

Your code does not access any server. It does not use FTP nor SFTP. It works with local files.

Anyway, there's no way to copy files from FTP to SFTP server directly, if FTP and SFTP are the only interfaces you have available.

You have to download the files from the FTP server and then upload them to the SFTP server.

For finding the latest file on the FTP server: Download the latest file from an FTP server
For FTP download, see Upload and download a file to/from FTP server in C#/.NET
For SFTP upload, see C# SFTP upload files
For moving the file, see How can I use FTP to move files between directories?

You can stream the files from the download directly to upload, to avoid storing the files to a temporary local file.

If you want to use built-in .NET FTP implementation (WebClient/FtpWebRequest) and a native .NET SFTP library (like SSH.NET), you can do something like this:

var ftpClient = new WebClient();
ftpClient.Credentials = new NetworkCredential("ftpuser", "ftppass");
var ftpUrl = "ftp://ftp.example.com/ftp/path/file.csv";
using (var ftpStream = ftpClient.OpenRead(ftpUrl))
using (var sftpClient = new SftpClient("sftp.example.com", "sftpuser", "sftppass"))
{
    sftpClient.UploadFile(ftpStream, "/sftp/path/file.csv");
}

To move the processed file:

FtpWebRequest ftpRequest = new FtpWebRequest(ftpUrl);
ftpRequest.Method = WebRequestMethods.Ftp.Rename;
ftpRequest.RenameTo = "/ftp/path/processed/file.csv";
ftpRequest.GetResponse();

Were those two SFTP servers, it would be bit easier with my WinSCP .NET assembly. What is not native .NET library though, but it can be used from SSIS. With WinSCP, the code will be like:

// Setup session options
SessionOptions sftpSessionOptions1 = new SessionOptions
{
    Protocol = Protocol.Sftp,
    HostName = "sftp1.example.com",
    UserName = "username1",
    Password = "password",
};

SessionOptions sftpSessionOptions2 = new SessionOptions
{
    Protocol = Protocol.Sftp,
    HostName = "sftp2.example.com",
    UserName = "username2",
    Password = "password",
    SshHostKeyFingerprint = "ssh-rsa 2048 xxxxxxxxxxx...="
};

using (Session sftpSession1 = new Session())
using (Session sftpSession2 = new Session())
{
    // Connect to SFTP 1
    ftpSession1.Open(sftpSessionOptions1);

    // Get list of files in the FTP directory
    string sftpRemoteDir1 = "/ftp/remote/path";
    RemoteDirectoryInfo dirInfo = sftpSession1.ListDirectory(sftpRemoteDir1);

    // Select the most recent file
    RemoteFileInfo latest =
        dirInfo.Files
            .Where(file => !file.IsDirectory)
            .OrderByDescending(file => file.LastWriteTime)
            .First();

    // Connect to SFTP
    sftpSession2.Open(sftpSessionOptions2);

    string sftpRemoteDir2 = "/sftp/remote/path";
    string sftpRemotePath2 = RemotePath.Combine(sftpRemoteDir2, latest.Name);

    // Transfer from SFTP 1 to SFTP 2
    using (Stream downloadStream = sftpSession1.GetFile(latest.FullName))
    {
        sftpSession2.PutFile(downloadStream, sftpRemotePath2);
    }

    // Move the source file to the "processed" folder
    string processedDir = "/sftp/remote/path/processed";
    string processedPath = RemotePath.Combine(processedDir, latest.Name);
    ftpSession.MoveFile(latest.FullName, processedPath);
}

Untested. WinSCP 5.19 or newer is needed.

Transferring file between two remote FTP and SFTP servers in SSIS/C# and archiving source files to another directory afterwards

1 Answers