> That info is definitely helpful Jason, Thanks.

If I may add: Please make use of stored procedures. Both on the output and
input side.

This will give you a consistent way to format the output and input. Thus,
you can make changes to your underlying tables and abstract the complexity
inside the stored procedure if necessary. This would prevent the problem of
doing a change to your database and then causing your nightly process to
blow up.

Also, on the import side of things, your stored procedure can see if the
record exists and do an update or just do an insert in the case it doesn't
exist. No need to run a query and find this out in your application code.
Thus, it will go much faster. Here's your pseudo code:

Create new batch (add record to batch table)
Create batch folder
Move files to be processed from dump/drop-off folder to batch folder.
Grab first entity's file from batch folder and repeat for each file.
{
Create record in batch-entity table.
Start a transaction
{
Prepare stored procedure for import.
Load each item from file into stored procedure.
Delete records for entity that were not touched by the batch.
Mark record in batch-entity table as completed and Commit OR
If failure Rollback, mark batch-entity with error code and move to the
next one.
}
Mark batch in batch table as completed.

Hope this helps a little more.

Jason Wharton