SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Push FTP / Latest Posts

RE: Push FTP

Jules Bonnot — Tue, 26 Apr 2011 15:29:16 GMT

using CLR procedure, much more secure,robust free and extensible solution herehttp://sql-library.com/?p=186

RE: Push FTP

Ed Thompson — Tue, 05 Jul 2005 11:05:00 GMT

Good article.

For additional thought, before tossing aside a third-party component solution due to cost and complexity consider this, based on your salary what did it cost you to create this solution? Did you document it well enough for the process to survive you without disproportionate maintenance cost? Don't forget to include all aspects of your time including, planning, researching, documenting, and testing plus estimating the cost of implementing robust error reporting. Also, if this process fails what about retry attempts. Both the error reporting and multiple retries are available with many FTP component packages.

The larger downside of your solution is the lack of resuability. If you had something else come up that could use this FTP code, you would have to cut and paste or rewrite it both of which would increase your total cost of ownership.

All of that said, your article has obviously adressed a large community of readers and provoked a lot of good conversation.

Good job!

RE: Push FTP

Charley Jones — Fri, 01 Jul 2005 12:23:00 GMT

Ya know, there are always two ways of looking at a problem. One person's push is another's pull.

Another version of the solution is to have server A publish the bak files to a directory configured for ftp. Then use the DTS FTP get task from server b to pull on a schedule. Kinda sounds like snapshot replication.Course, setting up a Web Service to dump the tables might be easier now adays. Truncate Development / Import from Production... And in Sql Server 2005, that becomes a lot easier...

RE: Push FTP

Yelena Varshal — Fri, 01 Jul 2005 09:02:00 GMT

Steve,

Great Article!

I would like to comment on the Darren Green's error checking question here. As I replied in the discussion for my article Tuesday when asked the same question: "The solutions here are a Proof-Of-Concept" . One has to implement their own error checking solutions based on their own requirements. As an idea I suggest what works for me. I use it in Pull FTP but it easily could be used the same way in Push FTP.

1. Get the file sizes and /or file names - whatever you like with FileSystemObject.

2. Do DIR on the remote FTP directory to a local text file. Parse the file using VBscript.

3. Compare file sizes /file names from Step 1 to Step 2. If they are not the same, send an SMTP email to operator.

STEP 1 - DOS command, STEP 2 and 3 - VBScript. If you need to switch from DOS to FTP and back:

TO SWITCH FROM VBSCRIPT TO DOS BATCH

strCommand = "mycommand.bat"strStyle = 1StrWait = FalseSet sh = CreateObject("WScript.Shell")ReturnCode = sh.Run(strCommand, strStyle, strWait)

TO SWITCH FROM DOS BATCH TO VBSCRIPT:

start /wait C:\windows\system32\ftp.exe <my FTP parameters here>myVBScript.vbs

start /wait C:\windows\system32\ftp.exe <my FTP parameters here>cscript myVBScript.vbs

Yelena

RE: Push FTP

Steve Jones - SSC Editor — Fri, 01 Jul 2005 08:39:00 GMT

I've mostly sent small files over a short WAN (3 hops), so I haven't seen any issues with performance. The largest files were 10's of MB, so not huge stuff. Error recovery isn't really there, this is more like sending via UDP.I know this isn't the best ftp object to use, even the ones with IE don't seem to perform great.I might check on a VB or .NET forum and ask what people are using there. They've probably had experience with something, which is what you need in a recommendation.

RE: Push FTP

Peter Paasch-242074 — Fri, 01 Jul 2005 08:01:00 GMT

Can someone point me in the direction of a com dll I can purchase to do the same thing. Or perhaps post some code I can use to get started. I have 36 remote SQL servers that I would like to bring data in house with. Error and failure data is important. We do not have a managed network so over the web is my best bet.

RE: Push FTP

Antares686 — Fri, 01 Jul 2005 07:44:00 GMT

The thing that always irked me thou with the built in FTP component is how poorly it performs. I deal with very larg files sometimes and with ftp.exe and set as binary transfer type versus the ftp component will have drastic differences in speed. I have tested a couple of components and have yet to see anyone reach the same speed but the majority do outperform the built in component. So I choose to use the command line interface and avoid the cost when I only have about half dozen transfers and their all static in nature.

RE: Push FTP

David A. Long — Fri, 01 Jul 2005 00:15:00 GMT

I implemented a SQL Server Agent job to do these FTP Push tasks from NJ to CA without DTS. I found that the FTP Put will max out the T-1 between these locations.

Have you seen this?

Andy

RE: Push FTP

Steve Jones - SSC Editor — Tue, 25 Jun 2002 09:35:00 GMT

I'd post some more information under the DTS forum and we'll try to help. Or under the replication section.Steve Jonessteve@dkranch.net

RE: Push FTP

seaguard — Tue, 25 Jun 2002 00:52:00 GMT

I want to ftp my snapshot which is generated by SQL Server 2000's Replication function,but my configuration doesn't work.can I ply for DTS?

RE: Push FTP

Steve Jones - SSC Editor — Tue, 02 Apr 2002 10:02:00 GMT

I've thought about using a COM component and am not opposed. The thing I really like about this method is I can copy a single file (.DTS) to a SQL Server and have it run as a task without any installations, registrations, reboots, etc. I can update this thing without affecting the server, and it works on every SQL Server.Kind of a KISS solution.thanks for the complement.Steve Jonessteve@dkranch.net

RE: Push FTP

ckempste — Mon, 01 Apr 2002 22:40:00 GMT

Nice article steve. I had the same problem a while ago, I did basically the same thing with a command file for the command line ftp with a cmdshell to run it, works ok but not very clean. in the end, I got a programmer to whip up a com routine for ftp, the beauty of this is that I can specify a specific port which the command line FTP.exe does not allow, therefore, making it very restrictive under some circumstances.

RE: Push FTP

Steve Jones - SSC Editor — Fri, 02 Nov 2001 10:33:00 GMT

Glad it will help. I've got some more details for how I handle the other side of the backups I will post soon.Steve Jonessteve@dkranch.net

RE: Push FTP

jamyer — Fri, 02 Nov 2001 09:43:00 GMT

Cool. I have a remote site that is about to rehost to W2K & S2K. Your solution will come in handy!

RE: Push FTP

Steve Jones - SSC Editor — Tue, 30 Oct 2001 09:02:00 GMT

I'll admit the error handling is low here. I could add some in ActiveX easily, though ftp.exe isn't great for doing this.I really wanted this solution because I can deploy it completely from sql without being at the server.Steve Jonessteve@dkranch.net

RE: Push FTP

Andy Warren — Tue, 30 Oct 2001 06:01:00 GMT

Kowabunga, someone who likes to compile code! Andy

RE: Push FTP

darren.green — Mon, 29 Oct 2001 10:51:00 GMT

You've wrapped it up nicely in ActX and no doubt it works great for you, but what about decent error information? I've been down this route but have converted to a COM DLL, so I can get meaningfull error messages. This was a no cost solution as I wrote the DLL and two seconds to install- copy file, register file.Darren Green

RE: Push FTP

Andy Warren — Mon, 15 Oct 2001 15:42:00 GMT

I agree about reboots - not an issue when you build your own...or as much of an issue anyway. I get a lot of use/reuse out of code in COM dll's.Andy

RE: Push FTP

Steve Jones - SSC Editor — Mon, 15 Oct 2001 14:51:00 GMT

Personally I hate installing new software on my servers. I built this solution so that I could quickly and easily move this solution to another server without worrying about registering components, rebooting, etc.In fact, I setup a new version of this DTS package on a server last week in about 3 minutes with no reboots.Steve Jonessteve@dkranch.net

RE: Push FTP

Andy Warren — Fri, 12 Oct 2001 17:32:00 GMT

Pretty interesting. I agree that it doesn't make a lot of sense that the DTS task only does one way transfers - I've looked at the API recently and there ain't a lot of difference between get and put.I like COM and compiled code for solutions, but for this I doubt it make much difference, would be style and not substance!Andy

Push FTP

Steve Jones - SSC Editor — Thu, 11 Oct 2001 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/pushftp.asp