Reliable FTP process for Data transfer
-
For our data warehouse, we receive database backups from the hosting company through FTP process.
FTP client (CuteFTP) being used at our end have some limitations since we have to launch it manually everytime server has to be rebooted. There are weekly downloads and daily download jobs in this tool that synchronizes the files of size upto 1GB. There are other jobs that run after these downloads and eventually populate the data warehouse.
Would you please recommend a more reliable way of running this process.
Thanks in advance.
-
What about using the FTP task in DTS?
-
If you want total control of the process and don't mind writing the process yourself (C, C#, VB.net, etc) then http://www.rebex.net/ftp.net/ has a great product. I have worked on two Data Warehousing projects where I had similiar requrements, but was required to use a "secure" form of FTP (which Rebex offers). I wrote a simple program that was run from Windows Scheduler and checked multiple sites for available files, when present it downloaded them and then invoked a DTS process to load the data into the staging area.
-
I'd have to agree with the FTP task in DTS, however, since the one that comes with SQL 2000 isn't all that great have a look at http://www.sqldts.com/302.aspx. They wrote their own DTS FTP task that can be installed on your server and works much, much better than the one that comes with SQL 2000.
-Luke.
-
Thanks for the suggestions. I was looking for more like an off-the-shelve product, which could be easily scheduled.
Since we are inclined towards customizing code for this task has anyone looked at BizTalk Server's FTP adaptor.
-
DTS jobs are fine with scheduling, however as you rightly mentioned before, the feedback about FTP component of DTS isn't too good. DTS_FTP tool you mentioned in your previous post does not seem to have file synchronization option.
-
I am suprised that you are having problems with CuteFTP. I have included their trasnfer engine in a .Net service and never had a problem with it. (yes this is legal per their license agreement.) Their help file has a section on all of this with examples.
I have also used the SP_OACreate stored procedures to automate some tasks (yes, I can see many of you cringe in fear) because DTS didn't have the PUT command (only had GET) and we needed better control.
SJ
-
I did this quite a bit and worked with a couple of different things. Generally, I was able to get a command-line sync tool (don't know if CuteFTP has one, but we used WS_FTP for a time, which did have one). I would pull down newer files from the remote site and remove them when finished processing.
Eventually, I needed more power/logging and scripted my processes in Python. I had written some custom methods that wrote to SQL Server log tables for each file in/out, was able to process all files in a folder with little trouble (after some tweaking) and could even do basic pre-processing such as creating a "trigger" file after the file was successfully downloaded. That indicated that DTS could then pick up and process the file. No trigger == No processing because the transfer may have been aborted part of the way through.
For scheduling, we just used the basic Windows Server scheduler to run everything. We were even able to cluster that, but with the need to ensure permissions are set on both nodes, that became painful. I can share more details on the overall process if desired, but this should give you some ideas to start with.
I would also put in a plug for the SQLDTS.com component for FTP to an extent. If the file names will change regularly, that could be painful as the file list is in XML. If not, it's a good start.
-Pete
-
I wrote the whole thing inside a sql job, using vb sripting to control the files and os commands for things like ftp.
5ilverFox
Consulting DBA / Developer
South Africa - "and don't mind writing the process yourself (C, C#, VB.net, etc)"Visual Studio 2005 (2.0) has built in FTP functionality so NO third party app is required.
See FtpWebRequest and FtpWebResponse
MISfIT -
ftp sounds great but it is not secure. The user ID and password are sent over the wire in clear text !!! Do a bit more homework and possibly spend a bit of money to get 'secure' ftp product - an encrypted ftp also known as sftp. Depending on your business and the type of data being moved your auditing or compliance departments may even pay for it and thank you as well !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thanks everyone. Very good ideas.
I'll go with the customized solution in .Net 1.1 using CuteFTP SDK, we haven't moved to VS2005 yet.
SFTP is also worth considering.
-
Could also consider using PGP over FTP. That was enough to satisfy our auditors. We used GnuPG for the PGP solution (with a front-end called WinPT for easier key management). This also provided easier interaction with our partners because they didn't need an SFTP client nor did we need one. Probably easier than it sounds, but I had trouble getting the SFTP sites working in our shop and thus the transition to PGP/FTP.
-Pete
-
We upgraded from VS 2003 1.1 to VS 2005 2.0 primarily for its built-in FTP over SSL. We also needed a secure connection. request.EnableSsl = True
MISfIT
Accepting all invitesViewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply