DTS

[kinetic123]

I have a table in SQL 2000 with a primary key constraint.

I have created a Transformation task to load the above table from a flat file.

The problem is that the flat file has duplicate records and the load fails because the primary key constraint in the table is violated.

Is there some method by which the the first non duplicate row is inserted and from then on the duplicated rows get rejected, withoutthe task failing.

 

[sonname]

Not sure if this will solve the problem, but there is an area in the DTS, I beleive there is an option called "Enable identity insert", under the tranform button that may allow you to do this.

 

[fluteplr]

You can also set dts to ignore a certain number of errors. The answer above will not solve your problem.
Another way that would take more work but would in my opinion be safer would be to use dts to load the data to another table designed to hold the data temporarily and then design a stored procedure to insert the table in the real table without the dups.

Something like
insert into myrealtable
select * from mytemptable where
not exists (select * from myrealtable where
myrealtable.key = mytemptable.key)