Automate SFTP File Transfer with SQL Server Agent and WinSCP

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 = P@ssword123 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:P@ssword123@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

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

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…

1. Name step
2. Set Owner
3. Add a Description
4. Steps

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

Click OK twice.

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

This isn’t what we want to see.

1. Right click on the Job again
2. View History

1. Expand the dropdown
2. Click on the Job Step
3. View Message

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.

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:P@ssword123@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.

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:P@ssword123@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.

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

1. Navigate to destination directory
2. Highlight files(s) to transfer
3. Drag file to destination

The Upload box opens

1. Expand Transfer settings… dropdown
2. Choose 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

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

To demonstrate, we’ll choose

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

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:P@ssword123@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:

Next Steps

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

• Implement SFTP File Transfer with SQL Server Integration Services
• Using SFTP with SQL Server Integration Services
• SQL Server Agent Tips
• WinSCP