Here is the situation I have: I am using DTS to convert a table T1 within a
SQL database to another table T2 within the same database. I am using SQL
Server 2000. Table T2 has a column t2_productid which has been specified
as an index so that the values within this column are unique.

Now sometimes T1 contains values that when mapped onto T2 result in
duplicate values for the t2_productid field.

I could solve this by:

a) Writing a sql query that eliminates duplicates before using DTS to
populate T2. OR

b) I could use a mechanism within DTS that continues despite the error
during insertion and proceeds anyway inserting the rows that it can.

I tried the second approach by writing a function for the "On Insert
Failure" which simply returned success. When I did this, the DTS task
succeeded but T2 was empty. What am I doing wrong? My batch size is set
to 0.

Thanks,

William

You could just set the Max error count to a suitable number, as this allows
the pump to keep going after a problem. However if using fast load, then the
insert commit comes into play, and 0 means all rows go as one batch, which
has been failed, so turn off fast load or drop use a insert commit size of
1.


--
Darren Green
http://www.sqldts.com

"William Pierce" <evalsinca (AT) hotmail (DOT) com> wrote