1

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.

enter image description here

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
6
  • So FTP or SFTP? Those are two completely unrelated protocols. + What kind of change did you do in WinSCP settings? + Show us WinSCP session log both from ETL and your manual test. Apr 3 '20 at 11:45
  • I suspect this may be because SQL Agent is running under a different account than you used when running the bat file interactively. I've had good success using the WinSCP .NET scripting interface via PowerShell as that give one full control over the session settings without configuring a session separately.
    – Dan Guzman
    Apr 3 '20 at 11:50
  • Dan thanks for the tip, I am looking into this. @MartinPrikryl here is a link to the log file: pastebin.com/f5Nbxxwv. The only change I made in WinSCP settings was updating the ppk authorization file.
    – Bradro
    Apr 3 '20 at 16:21
  • Change of a private key cannot cause WinSCP not be able to even connect. + What does the log show? I've asked for two logs. One from ETL and one from your manual test. Your local file contains 7 sessions! Apr 3 '20 at 17:26
  • @Martin, noob question. I'm not sure where the test log would be or etl log. Are you referring to the log from the SQL agent job?
    – Bradro
    Apr 3 '20 at 18:09
1

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.

1
  • 1
    You should accept it as an answer so that it won't come again in the list to get answer from community. Jan 30 at 7:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.