Using WinSCP with SQL Server Agent

By:   |   Comments (6)   |   Related: 1 | 2 | > SQL Server Agent


In past tips, Using SFTP with SQL Server Integration Services and SFTP in SQL Server Integration Services SSIS Package with Bitvise, we looked at how to use SFTP to transfer files via SQL Server Integrations Services (SSIS). 

We also looked at how to do SFTP using WinSCP, a free SFTP and FTP client for Microsoft Windows, with SQL Agent in this tip Automate SFTP File Transfer with SQL Server Agent and WinSCP.

In this article, we look at how to add more functionality to our WinSCP scripts and make troubleshooting issues a bit easier.


WinSCP has some more options we can explore to assist with troubleshooting.  In this tip we will look at a few examples.

Versions used:

  • Windows 10
  • WinSCP 5.17.2 / Build 10278

Let's start by reviewing how to have the WinSCP GUI generate templates for us.

Connecting to SFTP Server with WinSCP

Here is my connection info:

  • Host name:
  • User name: sftpuser
  • Password: supersecret

To launch WinSCP, run "C:\Program Files (x86)\WinSCP\WinSCP.exe" (presuming WinSCP is installed under "C:\Program Files (x86)\WinSCP\”) on the machine you'll be running transfer from. If you're testing from another machine, you'll just need to copy and paste the new RSA key.


  1. Host name
  2. User name
  3. Password
  4. Login
WinSCP Login Screen
  1. If prompted, select Yes to add host key to the cache.
Add Host Key to Cache Prompt

Generate Template

Let's generate a template to download a file. Open the WinSCP GUI and navigate to the local directory we want to download to. 

  1. Right click on file(s) to download
  2. Download…
  3. Download…
Download File
  1. Transfer Settings… down arrow
  2. Generate Code…
Download File Template
  1. Copy to Clipboard
Download File Script

Paste it into a text editor.

open sftp://sftpuser:[email protected]/ -hostkey="ssh-rsa 1024 BFk/gCd0dIBl9JN2Ej0VPEn6ZRDQ7E/73nFtcs465xQ="
cd /files
lcd G:\SftpFiles
get file2.txt
# Execute the script using a command like:
# "C:\Program Files (x86)\WinSCP\WinSCP.exe" /log="C:\writable\path\to\log\WinSCP.log" /ini=nul /script="C:\path\to\script\script.txt"

I've saved it as G:\SftpScripts\ WinscpDemo.sftp.

Download File Script

Next, copy the Windows command line section of the generated script into another text file.

  • Remove # commented lines
  • Add @echo off
  • Log to working drive
  • Cd to working directory
  • Change WinSCP.exe to ( is opens the console version of the app. I suspect this is a bug.)
  • Edit the /script to the .sftp file we just created.
  • Edit the name of log file. I've chosen G:\SftpScripts\SftpDemo.log.
  • Save the file. Ours is G:\SftpScripts\SftpDemo.bat.
@echo off
cd \SftpScripts
"C:\Program Files (x86)\WinSCP\" /log="G:\SftpScripts\WinscpDemo.log" /ini=nul /script="G:\SftpScripts\WinscpDemo.sftp"
Batch File to Call SFTP Script

Run WinscpDemo.bat.

We see the file transferred, the file in the directory and open WinscpDemo.log.

Run Batch File

Here we see file2.txt was transferred.

WinSCP Log File

Automatically Download Latest File(s)

What if we have a requirement to only download a new file?

The files named file1.txt, file2.txt… are randomly dropped in the /files directory on SFTP server We don't control the number in the file name or when they're dropped and always need to download the newest file. We'll check daily so a new file is defined as being less than 1 day old.

There are 2 files in the /files directory:

  1. files1.txt – last modified over 24 hours ago
  2. file2.txt – last modified less than 24 hours ago

Edit WinsftpDemo.sftp with the following:

  • Set local directory
  • CD to where files are located G
  • Add -filemask switch with parameters to the command line tell the 'get' command to only transfer files named starting with 'file' and any other characters with an extension of '.txt' that are older than 1 day

We'll add a couple of lines to set the local directory and change to the directory we want to download from, make the edits, add some comments and delete the commented-out command line. Change the 'get file2.txt' to 'get -filemask="*>1D" file*.txt' which says filter out files that are greater than 1 day old and are named file*.txt, * being a wildcard for any character, and pass the name(s) to get to download.

open sftp://sftpuser:[email protected]/ -hostkey="ssh-rsa 1024 BFk/gCd0dIBl9JN2Ej0VPEn6ZRDQ7E/73nFtcs465xQ="
# change local directory
lcd G:\SftpFiles               
# change remote directory to /files
cd /files               
# get files named starting with 'file' and any other characters with an extension of '.txt' that are older than 1 day
lcd G:\SftpFiles
get -filemask="*>1D" file*.txt 
# exit sftp client

Run WinscpDemo.bat and we see where file2.txt was transferred to where we told it to go on the local drive. Open WinscpDemo.log.

Output from WinscpDemo.bat

We see here where file1.txt was excluded because it's less than 1 day old and file2.txt was transferred.


Change Logging Level Verbosity, File Size and Retention

We'll go further and change the verbosity level of the log file. Currently, we're at the default of 0, but we can reduce or increase it by adding the /loglevel= switch to the command line in the .bat file. The options are -1 (Reduced), 0 (Normal), 1 (Debug 1) and 2 (Debug 2).

@echo off
cd \SftpScripts
"C:\Program Files (x86)\WinSCP\" /log="G:\SftpScripts\WinscpDemo.log" /ini=nul /script="G:\SftpScripts\WinscpDemo.sftp" /loglevel=-1

Another parameter that can be added to /loglevel switch, regardless of which logging level is chosen is to append a * to enable password logging.

@echo off
cd \SftpScripts
"C:\Program Files (x86)\WinSCP\" /log="G:\SftpScripts\WinscpDemo.log" /ini=nul /script="G:\SftpScripts\WinscpDemo.sftp" /loglevel=-1*

As shown here the password is now displayed in the log file.

WINSCP Log File with Password Logging

Whether we increased the logging verbosity or not, we probably don't want the log file to grow out of control.

Add the /logsize to the command line. We can use this to limit the size of the log file and roll it over up to 5 files. The file size is specified in bytes and you can optionally use K (kilobyte, M (megabyte) or G (gigabyte) after the file size to keep the command line a little cleaner.

Let's increase the verbosity to 2, keep 3 archived log files and limit the log size to 64 kilobytes.

@echo off
cd \SftpScripts
"C:\Program Files (x86)\WinSCP\" /log="G:\SftpScripts\WinscpDemo.log" /ini=nul /script="G:\SftpScripts\WinscpDemo.sftp" /loglevel=2 /logsize=3*64K

And here's our log file with 3 archived copies after running the script multiple times.

Script Directory Listing
Next Steps

We've touched on some basic and commonly used functionality with WinSCP. Following are some links that show you how to schedule a SFTP job and dig deeper into using WinSCP:

SFTP with SSIS and SQL Server Agent again:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 17, 2023 - 8:21:44 AM - Joe Gavin Back To Top (91392)
Happy it helped Satish.

Saturday, July 15, 2023 - 12:05:36 PM - satish K Back To Top (91390)
Thank you so much, I have been struggling with host key issue, you did save lot of time.
Thanks again.

Friday, April 9, 2021 - 6:40:51 PM - Joe Gavin Back To Top (88506)
Bob, I'm afraid that is the case. Not positive, but if you require encrypting a password, you may be able to if you generate a PowerShell script:

Friday, April 9, 2021 - 3:05:13 PM - Bob Back To Top (88504)
Joe - I realize you wrote this awhile back but I'm hoping you're still around to answer this. It seems to me that the session password is being stored unencrypted. Do I have that right or am I overlooking something?

Bob - SQL Dev

Wednesday, April 1, 2020 - 10:52:18 AM - Joe Gavin Back To Top (85240)

Thanks Paresh!

Wednesday, April 1, 2020 - 10:05:10 AM - PARESH MOTIWALA Back To Top (85237)

Awesome work buddy!

get free sql tips
agree to terms