dharrison
I have a SQL Server Agent Job whose purpose is to extract data from a database, massage it, create
an export file, and send the file via FTP to SME.  The FTP portion of the job is able to connect to
SME, log in, and execute some commands (e.g. cd, lcd) but can't Put (send) the file.

The FTP executed in SQL is: ftp ftp -a -n -d -s[biggrin]:\exports\FindingRover\ftpcmd.txt

The FTP command text file (ftpcmd.txt) look something like this:

open ftp.StorageMadeEasy.com
user USERNAME PASSWORD
put \LOCAL_DIRECTORY\LCAC_Export_File.csv /REMOTE_DIRECTORY/LCAC_Export_File.csv
quit


Within a few seconds the Put command returns:

PORT 0,0,0,0,16,63
Connection closed by remote host.


I can FTP the file using FileZilla and Core FTP with no trouble.

I tried to FTP from a Windows 7 command line but it doesn't work.  These messages are displayed
following the Put command:

200 Command okay.
150 File status okay; about to open data connection

The process never completes.

We've successfully used a similar SQL Agent process to send files to other FTP sites with no trouble.

Any insights, help, or suggestions would be greatly appreciated.


Quote
Igor
You need to remove '-a' parameter. This parameter is needed only when the FTP server is running on local machine.
Quote
dharrison
Thanks for your reply. I removed the -a switch but got the same results. It appears to hang on the PUT command. The same thing happens when I use the PUT command at the command prompt.
Quote
Igor
It fails because Windows command-line FTP client does not support the passive mode.
The active mode doesn't work for you.

You have 2 options:
1) use another FTP client.
2) try to make it work via active mode. It is possible only in some cases.

I recomend to use another FTP client.
For example, WinSCP FTP client supports scripting and defaults to a passive mode: http://winscp.net/eng/docs/scripting

Here is small example of usage WinSCP FTP client:
Code:
C:\Program Files (x86)\WinSCP>winscp.exe /script=C:/ftp_commands.txt


Script `C:/ftp_commands.txt` :
Code:
open ftp://username:password@ftp.storagemadeeasy.com
put C:\file.doc /file.doc
quit
Quote
ElOtraDon
Instead of using a port command, try changing to a passive mode command.  When I see a PORT command with a number of zeros like yours with PORT 0,0,0,0,16,63, I tend to suspect routers and firewalls.  Going into passive mode can help with routers, firewalls, and address translation issues.
Quote
dharrison
I tried using the Core FTP client. It worked great. Problem solved. Thanks Igor and
ElOtraDon for your assistance.
Quote