[thelist] Large Monthly File Imports and Exports

[TJ]

Hello Everyone -

I have a client who needs close to 4 dozen files imported on a monthly
basis.  The files are text (CSV or TXT) but I have the formats and they are
well documented and defined.   Problem is, the import files are huge.  Some
files are approaching 2G in physical size.  Some are 1/2 or 1/4 of that, but
still they are huge.

On a monthly basis these files will arrive, but not in a regular manner..
However, when they do arrive they want to put the file into a directory
somewhere and then they want to "push a button" that does the following:

1) import the files, individually (not simulataneously or multitasking-like)
2) export a subset of that file to and pre-defined text export file
3) produce reports telling the number of import records, export records,
discard/error records for each run.
4) front-end user interface that allows for viewing/printing reports and
rerunning any month's run, if necessary.

* for each run after the initial import/export, the export file can not have
any records that were already imported previously
* each import will only check for structural file problems, not content
within each record. the export will filter problem records (i.e. date fields
having garbage in them, null in what they consider the 'key' field for that
particular file, etc)  so the import can blast these records into the DB but
the export will be more controlled.

The client wants this done in Microsoft Access (2003).  I was able to load
one of the larger files into Access and it took 48 minutes on my machine
which is circa-1999/2000 (4G RAM).  Older machine...  The export took even
longer.   This is too long for the client.  They were hoping for 10-20
minutes max..

I am thinking more along the lines of SQL Server and maybe VB front end or
Access as the front end, or even ASP.   I dont have access to any dot-Net
technology or SQL Sever 2005 or 2008.  Aside from Access 2003, which I dont
wish to utilize, I can only offer ASP (IIS 6 with classic ASP), VB (6) or
Access (2003) with SQL Server 2000 as the DBMS.   The client, however, does
not have SQL Server 2000, only Office 2003 to be installed on the target
machine that will run this THING.

The Express editions of 2005 and 2008 are free to download and use but they
wont give them DTS (SSIS).  I have a developer copy of SQL Sevrver 2000
however I am unsure if this can be "distributed" with a setup program.

Does anyone have any ideas?  Anywhere to go?  I am thinking BCP or some bulk
import and slap a user interface on it with VB 6 and add some "control"
tables in SQL Server 2000, to allow them to rerun exports and to do other
logic, etc.  Even an ASP/Intranet sort of interface to SQL Server BUT they
dont have SQL Server 2000.

ANY ideas or suggestions would be greatly appreciated!

Cheers,
-Mike