Unanswered: MS SQL 2000 - DTS Package (insert valid records and skip invalid records)
I'll simplify the scenario. I designed a DTS Package.
Step1 is to copy records from Table1 to Table2.
Step2 is to send an email once process is done.
There might be some invalid data in Table1 so not all records can be copied. It’s ok, I just want all the other (valid) records to be copied though.
For example there are 10 records in Table1 to be copied to Table2. Record No. 4 cannot be copied because there is an invalid data (ex. null field in the destination table).
During DTS run, I want the 9 records to be copied though. Then I want Step2 to be executed even if there is an error in Step1. In short I want the process Step1 to continue copying and just skip the invalid record then proceed to Step2 thereafter. I can’t seem to find the right precedence constraints to use.
What can I do to accomplish my desired result. Thanks.
I would load the data into an interim / temporary table first with no contraints, then only select the records that conform to the contraints in the destination table. That way you don't have to deal with ignoring the errors.