FTP Upload using DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

FTP Upload using DTS

What is the recommended way of transferring files from my server to a remote FTP server. I assumed DTS FTP task would allow me to do this, but it appears it is only designed for downloading via FTP. Any suggestions please?
True, in this case just use DTS task to output to a flat file. _________
Satya SKJ

Yes, I do, but its the flat file I want to FTP to another location
Im hoping im not going to have to schedule a dos FTP to do the task, but I suspect I might have to
Using DTS and FTP to Push Files
http://www.sqlteam.com/item.asp?ItemID=12408 /Argyle
Thanks Argyle!
I have my FTP upload working now, thanks. Now I am faced with the problem that if the server is not responding, dos FTP simply stalls, and the CMD prompt never terminates.
Im looking for a way to either: Supply a timeout value to DOS FTP (not possible afaik)
Specify a timeout to xp_cmdshell (not possible)
Specify a timeout for the job as a whole (hmm, maybe possible?! But even so, will that necessarily terminate the child cmdprompt process) Failing this, does anyone know a cmdline FTP app that will allow a timeout to be supplied? Thanks, I know this is slightly off topic.

You can set a timeout on either a cmdExec step in a job or a cmdExec task in DTS. hth
Hi Jacco, thanks for the info, but after searching both BOL and examining EM interface, I cant find anyway to set this timeout in a job. Could you explain please ?
I gather there is no UI to set the timeout of a job/task. For FTP and TCP in general, you might want to tweak a TCP setting in the registry e.g. especially the [TcpMaxDataRetransmissions] key. You may get full text on doing this at http://support.microsoft.com/default.aspx?scid=kb;EN-US;120642] I haven’t tried it but I got the link from a similar thread at:
http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=8578]
Thanks for the advice. Im not confident about changing this registry setting on a production server though, mainly because the most frequent reason the ftpo task fails is not due to heavy network usage. Its a clients ftp server, and its not uncommon for them to screw up user accounts, server characteristics etc, which means my user account regularly goes belly up (I dont have to cater for this, all I want to make sure is that I try to send the data, and if it does fail, then it doesnt leave a CMD shell running perpetually on my server). Any more ideas from anyone please?
The only solution I can see is that I write a cmdline based FTP client which does allow a graceful timeout, but It would be nice to avoid this Thanks
If you know some VB and DTS scripting, Try this link: http://www.sqldts.com/default.aspx?6,102,251,0,1] No guarantees here but it might help. I should state that I haven’t tried it yet. Good Luck. NHO
Thanks vbkenya, this looks like the most promising solution so far!
Try it and let us know. I can’t confirm it’s usefulness now…a bit tied up in some spaghetti T-SQL. NHO
Chappy, if you want you can also have a look at the examples that are installed with SQL Server installation. Trust me they are very useful. Infact we had developed an VC++ application for import / export of data using DTS by taking guidance from those examples. Gaurav
]]>