Insert records on FTP server database

[Guest]

we are constructing a (sql 2005) database on an FTP server where we will need
to insert records into the tables. At this point our ftp scripts will allow
us to upload/download documents/file from the server.

We will be getting comma delimited records that must be inserted into the
database. I haven't seen anyone with an example of how this task might be
accomplished. Any one have and Idea? Does this sound like something that can
be done with streamreaders/writers?

 

[John Timney \( MVP \)]

Why are you using ftp, sounds ideally suited to an http upload and download.

 

[Guest]

Thank you John, consider this an oportunity to teach/inform.
I started with the FTP because that was the example I found in a book and
what I found on this site.
What makes the difference? Where is a link to an article explaining the
difference? Which is easier to use?

 

[John Timney \( MVP \)]

ftp (http://www.w3.org/Protocols/rfc959/) is a bit of a funky protocol
usually running on server port 21 and typically requires an ftp client, but
there are some good components for browser based solutions such as:

http://www.dart.com/samples/ftpdotnet.asp

http upload (http://www.faqs.org/rfcs/rfc1867.html) is probably much better
suited to what your describing as there is a control in asp.net specifically
for this, and very easy to use - needs no client as it works in a browser
over port 80 and can be secured easily over https. Also, its one less
protocol to expose on your webserver, so less out of the box risk.

http://www.asp.net/QuickStart/aspnet/doc/ctrlref/standard/fileupload.aspx

Upload your files and save them locally, parse them and stick them in the DB
on the server all in the same script. Should take you about 20 minutes to
write........lol (only kidding) - but there are some good csv parsing
examples

http://www.codeproject.com/cs/database/CsvReader.asp

 

[Guest]

Just to make sure we understand the whole thing. You *Must* have a UI for
this? AND You *Must* immediately have the file imported?

Reason asking... It is routine where I work for SQL DTS packages to BOTH
fetch files (using Secured FTP) as well as import them into the db. If a ui
is required, but timing is not as important, you could still have a DTS just
pick up the files.

We like to do files in pairs. The data file followed by a trigger file.
The receiving process waits for the trigger file to exist, then imports the
data file, and deletes only the trigger file. This way you aren't destroying
the data, or wasting time/space copying it to a temporary backup.

Just exploring the process more.