Hello all:

I'm probably too close to this to see the solution so I'm hoping you guys
can help. I have an import loop that will import 0..n files (similar to the
one on www.sqldts.com but modified quite a bit to allow for e-mail
notification, sequential processing by file date, use of logparser, etc).
The table structure is a simple File table with name, size, md5 hash, etc in
it and a record table with the actual record information. One problem we
have is a file that has already been processed cannot be processed again
(thus the reason for the file table). Now the import of the records could
fail due to malformed data (happens occationally due to the human error
factor). In that case, the file is renamed for manual review and is skipped
by the routine. When this happens, I must go look at the file, correct it
and rename for a reprocess. I don't want to have to go to the DB and delete
the file record to allow this (once a file is written to the file table it
will never allow that file to reprocess). So the idea was to insert the
file record only after a successful import. Only problem is that the file
ID must be assigned to all rows inserted into the record table during the
import. Is there a way to retro assign the File_ID to the just imported
rows like in the On Batch Complete multi-phase datapump or otherwise? Can
an assign by completed to each inserted row in the batch (one batch per
file) before commit to the DB? The only solution I'm seeing right now is to
insert the file into the file table and delete it back out if an error
occurs. I'm not really wanting to do this as it modifies the workflow quite
a bit as well as the actual code. Got any better solutions?

Brainstorming welcome........

Thanks,
Scott

P.S. Both file and record tables have identity columns as primary keys.