Programing quesiton: picking up a FTP'd file for processing....

[Imagination]

Hello,

Bit of description:

At work (a bank), we have 3 production servers:

1 Webserver
1 Application servers
1 SQL Server

We offer for certain clients an ability to import payments. They create a file (using what ever spreadsheet or app they want) based on a file spec that the bank designed. they log into the website and then upload the file onto our webserver. Once its on the webserver, a record is entered into a table on our SQL Server w/ a pending status. We have a background process (built in VB6) that looks every couple of minutes in that table and sees if theres any items pending. If there are, it then locates the file on the webserver and processes the payments in the file and thats that.

Now, a couple of the customers want to create a system on there end that will FTP the files directly to our webserver w/o any manual intervention.

My question is does anyone have any recommendations on how I should go about modifying the application that runs on the app server to also look for new files created besides the records in our SQL database?

We are not on .Net or anything like that (that conversion is 1 year away) because our bank beleives in "if it ain't broke, don't fix it".

This is just ASP, VB6, C++, SQL Server 2000...

 

[mundane]

Caveat: I don't have much experience in securing webservers.

You could have a cron job run regularly, scan the directory, and process the imports. I would recommend SFTP over FTP since this is an automated process, maybe even wrap the file in PGP so you know for damn sure those payments are coming from your client.

 

[Aikouka]

What it sounds like to me, is that you need a nice efficient way to segregate the files that have been processed against the files that have not been processed (all this relating to the ftp uploaded files).

Now, what I'm wondering is... do you have to keep the files when you're done with them? If so, what happens when they're done being processed?

This could be pretty simplistic if you don't keep the files (or move them to an archive of sorts). You'd literally have it check the database first, process those, and then process the leftovers as those're new non-databased files.

If you cannot move files, it may be easier to have all these spreadsheets uploaded to a separate directory to simplify the process. Then, when those are processed, you could add them to the database and move them. Or simply upload them to the folder and check the modified date.

 

[Imagination]

Hi,

Bit more detail:

1. User uploads file to wwwroot/uploads folder. At same time, a COM is called and enteres a record into our import table w/ FileID (generated), FileName, UserID, CorpID, Date/Time and Status (PEN).

2. VB Process on APP server looks for Status to be PEN and then goes to wwwroot/uploads on webserver and picks up file and processes the file (loading the records in a completely different table for the user to later on approve and send out into the world).

3. Once processing is done, the status field is given status of PRC (processed) and the file on the webserver is then deleted.

each customer has there own unquie CorpID and inside each file in the header record is the CorpID. So no mater what file is uploaded, by opening the file up, i'll always know who the file belongs to. Currently we have 50+ customers we have given this import function to and about 7 or 8 of them have all wanted this FTP version.