SFTP multiple tables

Question

We are looking for a way to compress hundreds of tables and send them via sftp to another server. We are thinking of using SSIS but SSIS will not use sftp without some type of add on. What is another--possibly better-- alternative?

Additional Requirements

• There should be one zip file per table.
• A table-driven approach will be used to determine which tables will be transferred. I.e., a table will contain the name of each table to be transferred.

I think these two requirements would be difficult to implement with SSIS. For example, a batch script would be necessary to do the zipping. Then we would have both a batch script and some c# code to maintain. Perhaps another solution is in order..

Hypothetical Question

Would this be more efficient (faster) than having hundreds of SSIS packages, each performing the same SFTP transfer on different tables at the same time? The thought being that the server would run multiple packages concurrently where as this method may only run one convert, zip, and transfer at a time.

Answer 1

This is how I was able to accomplish this using SSIS. I created a C# DLL that could be referenced in a script task. I did it this way so that I could also use my class library to SFTP from other applications. So if you dont want to use SSIS you can do an alternative like setting up a windows service.

First I create a reference to WinSCPNet.dll, found here:

https://winscp.net/eng/docs/library

Here is the code I created. It is still in a prototype form, you will need to do some things like add proper error handling / logging where I am just using Console.Writeline.

public class Sftp
{
    public static int ListFiles(string Password, string HostName, string UserName, string SshHostKeyFingerprint)
    {
        try
        {
            // Setup session options
    SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = HostName,
                UserName = UserName,
                Password = Password,
                SshHostKeyFingerprint = SshHostKeyFingerprint
            };

            using (Session session = new Session())
            {
                // Connect
                session.Open(sessionOptions);

                RemoteDirectoryInfo directory = session.ListDirectory("/");

                foreach (RemoteFileInfo fileInfo in directory.Files)
                {
                    Console.WriteLine("{0} with size {1}, permissions {2} and last modification at {3}",
                        fileInfo.Name, fileInfo.Length, fileInfo.FilePermissions, fileInfo.LastWriteTime);
                }
            }

            return 0;
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: {0}", e);
            return 1;
        }
    }

    public static int GetFiles(string Password, string HostName, string UserName, string SshHostKeyFingerprint)
    {
        try
        {
            // Setup session options
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = HostName,
                UserName = UserName,
                Password = Password,
                SshHostKeyFingerprint = SshHostKeyFingerprint
            };

            using (Session session = new Session())
            {
                // Connect
                session.Open(sessionOptions);

                // Download files
                TransferOptions transferOptions = new TransferOptions();
                transferOptions.TransferMode = TransferMode.Binary;

                TransferOperationResult transferResult;
                transferResult = session.GetFiles("/home/user/*", "d:\\download\\", true, transferOptions);

                // Throw on any error
                transferResult.Check();

                // Print results
                foreach (TransferEventArgs transfer in transferResult.Transfers)
                {
                    Console.WriteLine("Download of {0} succeeded", transfer.FileName);
                }
            }

            return 0;
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: {0}", e);
            return 1;
        }
    }

    // Copy and pasted code shared with PutFile, refactor if making changes.
    public static int PutFiles(string Password, string HostName, string UserName, string SshHostKeyFingerprint, string SourceFolder, string RemoteFolder, string FileMask, string WinScpExePath)
    {
        try
        {
            // Setup session options
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = HostName,
                UserName = UserName,
                Password = Password,
                SshHostKeyFingerprint = SshHostKeyFingerprint
            };

            using (Session session = new Session())
            {
                // Connect
                session.ExecutablePath = WinScpExePath;
                session.Open(sessionOptions);

                // Upload files
                TransferOptions transferOptions = new TransferOptions();
                transferOptions.TransferMode = TransferMode.Binary;

                TransferOperationResult transferResult;
                transferResult = session.PutFiles(SourceFolder + FileMask, RemoteFolder, false, transferOptions);

                // Throw on any error
                transferResult.Check();

                // Print results
                foreach (TransferEventArgs transfer in transferResult.Transfers)
                {
                    Console.WriteLine("Upload of {0} succeeded", transfer.FileName);
                }
            }

            return 0;
        }
        catch (Exception e)
        {
            Console.WriteLine($"Error: {e}");
            return 1;
        }
    }
    // Copy and pasted code from PutFiles, refactor if making changes.
    public static int PutFile(string Password, string HostName, string UserName, string SshHostKeyFingerprint, string InputFile, string RemoteFolder, string WinScpExePath)
    {
        try
        {
            // Setup session options
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Sftp,
                HostName = HostName,
                UserName = UserName,
                Password = Password,
                SshHostKeyFingerprint = SshHostKeyFingerprint
            };

            using (Session session = new Session())
            {
                // Connect
                session.ExecutablePath = WinScpExePath;
                session.Open(sessionOptions);

                // Upload files
                TransferOptions transferOptions = new TransferOptions();
                transferOptions.TransferMode = TransferMode.Binary;

                TransferOperationResult transferResult;
                transferResult = session.PutFiles(InputFile, RemoteFolder, false, transferOptions);

                // Throw on any error
                transferResult.Check();

                // Print results
                foreach (TransferEventArgs transfer in transferResult.Transfers)
                {
                    Console.WriteLine("Upload of {0} succeeded", transfer.FileName);
                }
            }

            return 0;
        }
        catch (Exception e)
        {
            Console.WriteLine($"Error: {e}");
            return 1;
        }
    }
}