Situation
We use WinSCP to receive daily csv files from a vendor which we then extract and load into our data warehouse. To achieve this, we have a SQL Agent scheduled job that executes a process task that kicks off a batch file. This batch file then runs a FTP script that downloads the folders from WinSCP to a local drive which later goes through the ETL process.
Complication
Recently our vendor updated their FTP site. We were required to update the session in WinSCP. I updated the session and can log into the WinSCP session manually (by double-clicking .bat file from a local box) but when SQL server agent kicks off the batch process via executing process task I receive the following error.
Network error: Connection to "ftp.hostsite.com" timed out
The server rejected the SFTP connection, but it listens for FTP connections.
Did you want to use the FTP protocol instead of SFTP? Prefer using encryption.
Batch File
"\\sharedfolder\winscp.exe" compdata /console /script=\\sharedFolder\Data\App\DownloadFlatFile.ftp
DownloadFlatFile.ftp
get /home/*.csv \\sharedfile\Data\
mv /home/*.csv \\sharedfile\Data\
exit```
Here is WinSCP session info.
File protocol: SFTP
Hostname: ftp.site.com
Port number: 22
Username: privateusername
Password: none
Then under advanced settings >> SSH >> Authentication I have the path to the ppk file
Best Answer
Turns out the issue was the firewall. I figured out that with this setup, SQL server runs remotely and the server it runs off of had a firewall that was blocking the FTP site's IP address. The network admins were able to whitelist the IP address, now everything runs correctly. Thanks to those that helped me work through the problem.