Automate SFTP File Transfer with SQL Server Agent and WinSCP
By: Joe Gavin | Updated: 2020-01-16 | Comments (6) | Related: 1 | 2 | More > SQL Server Agent
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.
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
- You're running SQL Server Standard Edition or higher
- WinSCP is installed
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"
- Open a command prompt and call sftptest.bat
- 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
- 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.
- In SQL Server Management Studio, right click on SQL Server Agent
- Name step
- Set Owner
- Add a Description
- Name Step
- Chose Operating system (CmdExec) in the Type dropdown
- Enter the full path to C:\SftpTesting\SftpScripts\sftptest.bat
Click OK twice.
- Right click on the Job
- Start Job at Step…
This isn't what we want to see.
- Right click on the Job again
- View History
- Expand the dropdown
- Click on the Job Step
- 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:[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.
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.
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.
- Select SFTP in File Protocol dropdown
- Enter Host name
- Port number
- Enter User name
- Enter Password
- Click Login
- Navigate to destination directory
- Highlight files(s) to transfer
- Drag file to destination
The Upload box opens
- Expand Transfer settings… dropdown
- Choose Generate Code…
And the Generate transfer code window opens.
We can now choose an option:
- 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
- Batch file
- 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:[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:
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
Last Updated: 2020-01-16
About the author
View all my tips