There are two steps:
- Setup the SFTP process, then
- Automate the SFTP process.
If you're a Unix client, use the SCP command. For SQL servers, here is a useful topic on the subject: http://haacked.com/archive/2006/04/21/sqlstoredproceduretoftpfilesfromsqlserver.aspx .
For SFTP, change the port from 21 to 22 (SFTP).
"We created a separate file and web entry form to collect/update the data. We extract data every night, ship it to a Linux box for checking errors and post processing, and then shove it to the external/ENS server using the Unix program \"curl.\"
We also have a perl program or two that lets us select records with incomplete or erroneous information (illegal phone number format) which we can optionally e-mail to the \"offending\" parties."
For additional case studies, please refer to Case Studies and Scripts: Datatel Colleague, SQL, Studio, Envision here.