Automate SFTP File Transfer with SQL Server Agent and WinSCP


By:   |   Updated: 2020-01-16   |   Comments (6)   |   Related: 1 | 2 | More > SQL Server Agent

Problem

You need to create an automated process to transfer files using Secure File Transfer Protocol (SFTP) with SQL Server Agent.  Check out this article to learn how.

Solution

These are the versions used for our examples:

  • Windows 10 Enterprise 1809
  • SQL Server 2017 Developer Edition
  • WinSCP 5.15.9
  • OpenSSH for Windows_8.1 SFTP server

Assumptions

  • You're running SQL Server Standard Edition or higher
  • WinSCP is installed

Scripting SFTP

Scripting with an SFTP client is very similar to scripting with an FTP client.  To get started, I've saved the following to C:\SftpTesting\SftpScripts\sftptest.txt to use as out SFTP client input file. It will be connecting to SFTP server mysftp.com with user sftpuser and password = [email protected] to transmit a text file called file1.txt from C:\SftpTesting\SftpFilesToTransmit to the SftpRoot\MyFiles directory.

# open connection to sftp server
open sftp://sftpuser:[email protected]@mysftp.com
 
# set file transfer mode to ascii or binary
ascii
 
# change directory
cd SftpRoot\MyFiles
 
# transmit files from local machine to sftp server
put C:\SftpTesting\SftpFilesToTransmit\file1.txt
 
#close connection
close 
 
#exit winscp
exit

Next, we'll need a method to call the SFTP client and input script, so we'll save this to C:\SftpTesting\SftpScripts\sftptest.bat.

:: log to drive
C:
:: change to working directory
cd \SftpTesting\SftpScripts
 
:: call SFTP client with input file
"C:\Program Files (x86)\WinSCP\WinSCP.com" /script="sftptest.txt"
  1. Open a command prompt and call sftptest.bat
  2. Enter a 'y' at the prompt if the server's host key isn't cached and if you're sure you're connecting to the correct host to cache the key and connection will commence
  3. And there is our file
Run Batch File

If we execute the batch file again, we won't be prompted for the key.

Run Batch File Again

SQL Server Agent Job

The point is to automate this so let's create a SQL Server Agent Job to call it.

  1. In SQL Server Management Studio, right click on SQL Server Agent
  2. New
  3. Job…
Create SQL Agent Job
  1. Name step
  2. Set Owner
  3. Add a Description
  4. Steps
Configure SQL Agent Job
  1. New
  2. Name Step
  3. Chose Operating system (CmdExec) in the Type dropdown
  4. Enter the full path to C:\SftpTesting\SftpScripts\sftptest.bat
Create SQL Agent Job Step

Click OK twice.

  1. Right click on the Job
  2. Start Job at Step…
Run SQL Agent Job

This isn't what we want to see.

Failed SQL Agent Job Output
  1. Right click on the Job again
  2. View History
View Job History
  1. Expand the dropdown
  2. Click on the Job Step
  3. View Message
Job Error

The "server's host key was not found in the cache" looks familiar. We've seen it before when we manually ran the batch file and chose 'y' to cache the key.

Host Key Error

The SQL Server Agent has no way to respond to the prompt so how can we fix it? Easy, all we need to do is add the -hostkey= switch along with the key to the open line in our input file.

# open connection to sftp server with hostkey
open sftp://sftpuser:[email protected]@mysftp.com -hostkey="ssh-ed25519 256 zl3rDcRp/3ekABilaWRgzrELzm5dwOluW15VtrytPMc="
 
# set file transfer mode to ascii or binary
ascii
 
# change directory
cd SftpRoot\MyFiles
 
# transmit files from local machine to sftp server
put C:\SftpTesting\SftpFilesToTransmit\file1.txt
 
#close connection
close 
 
#exit winscp
exit

Run the Job again and this is what we expect to see in the Start Jobs and Log File Viewer windows.

Successful SQL Agent Job Output
Successful SQL Agent Job Log

Add some usefulness

We have our basic steps down so let's make this a little more useful.

There are nine files named C:\SftpTesting\SftpFilesToTransmit name file1.txt, file2.txt, file3.txt, file4.txt, file5.txt, file6.txt, file7.txt, file8.txt, and file9.txt and we want to transfer all nine of them to the SFTP server. This is as simple as changing the '1' in the file name file1.txt to a single character '?' wildcard so it's now file?.txt.

# open connection to sftp server with hostkey
open sftp://sftpuser:[email protected]@mysftp.com -hostkey="ssh-ed25519 256 zl3rDcRp/3ekABilaWRgzrELzm5dwOluW15VtrytPMc="
 
# set file transfer mode to ascii or binary
ascii
 
# change directory
cd SftpRoot\MyFiles
 
# transmit files from local machine to sftp server
put C:\SftpTesting\SftpFilesToTransmit\file?.txt
 
#close connection
close 
 
#exit winscp
exit

And we can see in the log where all nine files were transmitted.

All Files Transferred

It could be very useful when troubleshooting a problem to have a local log file. This is easily accomplished by adding a /log switch and managed by adding /loglevel and /logsize switches to the WinSCP.com command line in sftptest.bat.

:: log to drive
C:
:: change to working directory
cd \SftpTesting\SftpScripts
 
:: call SFTP client with input file and log to local directory
"C:\Program Files (x86)\WinSCP\WinSCP.com" /script="sftptest.txt" /log="SftpTest.log" /loglevel=-1 /logsize=5*100K

/script ="sftptest.txt" - creates a log file called sftptest.log in the local working directory

/loglevel=-1 - reduced logging -1, 0, 1, 2 for Reduced, Normal, Debug1 and Debug2 logging levels respectively

/logsize= - 5*100K – limits number of rollover logs to 5 and limits log file size to 100KB (can be specified with K(iloByte), M(egaByte), G(igaByte))

Useful Functionality in WinSCP

Now that stepped through manually creating an automated SFTP file transfer let's look at some very useful functionality in WinSCP. Run WinSCP.exe to open the GUI client.

  1. Select SFTP in File Protocol dropdown
  2. Enter Host name
  3. Port number
  4. Enter User name
  5. Enter Password
  6. Click Login
WinSCP Login Screen
  1. Navigate to destination directory
  2. Highlight files(s) to transfer
  3. Drag file to destination
WinSCP File Transfer

The Upload box opens

  1. Expand Transfer settings… dropdown
  2. Choose Generate Code…
WinSCP Generate Code

And the Generate transfer code window opens.

We can now choose an option:

  1. Script
  2. Choose Script file, Batch file, Command-line or PowerShell script
WinSCP Code

Or under the .NET assembly code tab we have C#, VB.NET or PowerShell.

WinSCP Code

To demonstrate, we'll choose

  1. Script
  2. Batch file
  3. Copy to Clipboard
WinSCP Code

Paste Clipboard into text editor of choice. Change the working directory and log file name and directory to match our previous example as shown by comments.

@echo off
:: set working drive and directory
C: 
cd \SftpTesting\SftpScripts
 
:: change log file name and location
"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
  /log="SftpTest.log" /ini=nul ^
  /command ^
    "open sftp://sftpuser:[email protected]@mysftp.com/ -hostkey=""ssh-ed25519 256 zl3rDcRp/3ekABilaWRgzrELzm5dwOluW15VtrytPMc=""" ^
    "lcd C:\SftpTesting\SftpFilesToTransmit" ^
    "cd /C:/Users/sftpuser/SftpRoot/MyFiles" ^
    "put *" ^
    "exit"
 
set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
  echo Success
) else (
  echo Error
)
 
exit /b %WINSCP_RESULT%

We can get rid of our old log files and execute the batch file:

Run New Batch File
Next Steps

Following is further information on SFTP, WinSCP, and SQL Agent:



Last Updated: 2020-01-16


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, January 16, 2020 - 2:37:20 PM - Joe Gavin Back To Top

Thanks for the comment Scott. I impleneted this on 1 machine and didn't think of that.


Thursday, January 16, 2020 - 2:33:59 PM - Joe Gavin Back To Top

Thank you Edul.


Thursday, January 16, 2020 - 2:33:17 PM - Joe Gavin Back To Top

Thanks for your insight Adrian.


Thursday, January 16, 2020 - 9:01:14 AM - Adrian Hernandez Back To Top

Excellent tutorial. Thanks for sharing. I may add that WinSCP, by default uses the Auto switch, this supposedly sets text or binary automatically. I have experienced issues that sometimes it fails to set binary and the resulting file is unreadable by the target users. To overcome this, if sending files via script always specify text or binary.


Thursday, January 16, 2020 - 8:55:55 AM - Edul Chikhliwala Back To Top

Dear Sir:

Thank you for this very useful article. Very clearly written and will be of great use in my day-to-day work.

Please keep up the good work!

Edul


Thursday, January 16, 2020 - 8:15:09 AM - Scott Stanek Back To Top

Could have used this about six months ago, SFTP situation where I am getting files. Still nice to know that someone else embraces the WinSCP method I did. I will add that you will need to install the same WinSCP version used on your development server as your production server and jump through whatever hoops are needed to get that in place first. In fact, I suggest making the batch file manual version work on both environments BEFORE going to the effort to code the rest.



download


Recommended Reading

Using WinSCP with SQL Server Agent

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

How to start SQL Server Agent when Agent XPs show disabled

Managing SQL Server Agent Job History Log and SQL Server Error Log





get free sql tips
agree to terms


Learn more about SQL Server tools