Q:

Get millions of records from fixed-width flat file to SQL 2000

Obviously I can use BCP but here is the issue. If one of the records in a Batch have an invalid date I want to redirect that to a separate table/file/whatever, but keep the batch processing running. I don't think SSIS can be installed on the server which would have helped.

A:

Create a trigger that processes on INSERT. This trigger will do a validation check on your date field. If it fails the validation, then do an insert into your separate table, and you can also choose to continue the insert or not allow it to go through.
an important note: by default triggers do not fire on bulk inserts (BCP & SSIS included). To get this to work, you'll need to specify that you want the trigger to fire, using something like:
BULK INSERT your_database.your_schema.your_table FROM your_file WITH (FIRE_TRIGGERS )

A:

Yeah, if you are using DTS, you should just import into a staging table that uses varchar instead of dates and then massage the data into the proper tables afterwords.

A:

The problem with What Matt said is that you should not use a cursor to manipulate the data afterwards especially if you have millions of records. CUrsoprs are extremely inefficient and should be avoided.
Use batch processing instead.
But by all means use his idea of a staging table. I wouldn' ever consider importing directly into a production table as too many things can happen over time to change the data in the input file and cause problems.

