DTS: File import with error logs and uninterrupted import
-
I have multiple files(in one folder) to import into say tableA.
we have 10,000 records in all of these files. The import should be uninterrupted by any error in the transformation like say there was a datatype mis-match in one row then it should be logged in a text file and continue with the next row to import
do anyone have any idea how to do this
Thanks in advance
-
Best solution would be to import the data into a staging table in SQL Server that has all fields defined as varchar. Then you can run multiple SQL statements to validate and correct, or remove, bad data before inserting it into the production table.
--------------------
Colt 45 - the original point and click interface
-
As you noted, doing row-by-row comparisson is not efficient at all. If you switch to a set based method you'll find things will run much quicker.
For your error handling, add an extra field that you populate during your validation (could be an error message or flag, whatever you think necessary). Then you just insert all records where that field is null. To get your error report you just select all records where that field is not null.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply