How to download files from Secure FTP (SFTP)

Good day experts,

I need to automate the downloading of files from an SFTP location.  I have the SFTP host name and folder location to poll for new files.

My workflow basically need to be:
Connect to SFTP and browse the specified folder
Download each file to a local folder
For each successful download, delete or move the original file on SFTP

This process will be executed several times per day.

Please assist / recommend the easiest way to implement this.  I have basic skills in SSIS and C# but neither offer native support for SFTP operations.

Many thanks
ApplePietAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
I use a library from XCeed: http://xceed.com/SFTP_NET_Intro.html
ApplePietAuthor Commented:
Just started using this one:   http://sshnet.codeplex.com/

Got some of the basics to work so far but it's going slowly - the documentation is good but there are no examples I can find.
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi

Maybe i am completely offtopic but why not create a batch file that runs (WIN)SCP which is scheduled a few times a day?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

ApplePietAuthor Commented:
@Patricksr1972

Thanks - that did cross my mind but I tend to stick what I know best.  I thought that I would be better off with either SSIS or a custom C# application.

I looked at a few WinSCP examples but I couldn't really figure them out.

With C# i have the capability of easy error handling and writing log data back to SQL Server or a windows event log.

If you think WinSCP can work easily as well, you're welcome to point me to site, resource or example and I'll look into it and compare with what I'm currently doing.

Thanks
Patrick BogersDatacenter platform engineer LindowsCommented:
Cool, let me login to one off the servers i have configured for running like this and i get back to you...
ApplePietAuthor Commented:
@Éric Moreau

Exceed looks good but it's somewhat expensive though.   Our project did not budget for purchasing components like this.  Our sales team expected to be able to integrate with FTP using native .Net.  They did not consider SFTP.

With the exchange rate, required license for exceed will cost our project in the region of  ZAR 25000.

I will need to look at something less expensive.  

Exceed definitely looks good for future projects though.

Thank you
Patrick BogersDatacenter platform engineer LindowsCommented:
Ok,

So i have got a (scheduled) batch file with this content:

winscp.exe /script=script.txt

In the script i run this:

option batch on
option confirm off
open SFTPACCOUNTNAME@<ipadress>:22 -privatekey=PrivKey.ppk
option transfer binary
get file-<filename> c:\put-the-files-here\
close
exit



Then, from the batch OR the script you can add copy/move commands to transfer your files where desired.
HTH
ApplePietAuthor Commented:
@Patricksr1972:

Wow that actually quite ....    short.

Let me try that out also.   I actually logged on now to report that my solution now works (will post that now).

What is the private key in your script?

Thanks again
ApplePietAuthor Commented:
Hi everyone,

So I have an update:  I now have a solution that looks like it works.

I ended up writing a .Net C# Console application that references a 3rd party library for SFTP access.

Find info on the library here:   http://sshnet.codeplex.com

My code for using it (after adding a reference to the downloaded library).

       static void Main(string[] args)
        {


            SftpClient objSFTP = new SftpClient(getSetting("SFTPHost"), getSetting("SFTPUID"), getSetting("SFTPPwd"));
            SftpFile objPollDir;
            IEnumerable<SftpFile> dirFiles;
            Stream objFileStream;
            string strRootFolder = getSetting("SFTPRootFolder");
           
            objSFTP.Connect();

            objPollDir = objSFTP.Get(strRootFolder);
            dirFiles = objSFTP.ListDirectory(objPollDir.FullName);

 

            foreach (SftpFile xFile in dirFiles){

                if (xFile.IsRegularFile){

                    Console.WriteLine(xFile.FullName);
                    objFileStream = new FileStream(getSetting("LocalDLFolder") + xFile.FullName.Replace(strRootFolder, ""), FileMode.OpenOrCreate);

                    objSFTP.DownloadFile(xFile.FullName, objFileStream);

                    objFileStream.Flush();
                    objFileStream.Close();
                    objFileStream.Dispose();

                    xFile.MoveTo(getSetting("SFTPCompleteFolder") + xFile.FullName.Replace(strRootFolder, ""));
                }
            }

            objSFTP.Disconnect();
            objSFTP.Dispose();
            System.Threading.Thread.Sleep(1000);
        }

--------------------------------------------------------------------------------------------------------

getSetting referes to a private sub routine that just retrieves a setting value from the application's default settings file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick BogersDatacenter platform engineer LindowsCommented:
Hi, the ppk contains your private key which you generate and save as <ftpsaccountname>.ppk.
ApplePietAuthor Commented:
Worked best
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSH / Telnet Software

From novice to tech pro — start learning today.