Re: newbie duplicate delete dts question

#1

In article <041a01c35c41$7f43b840$a301280a (AT) phx (DOT) gbl>, Geoffrey Baker
<gbaker (AT) gwi (DOT) net> writes

Quote:

Hi, I am sure this is an often-asked question, but I
couldn't find any appropriate answers by searching this
newsgroup.

I have a dts script that grabs sql data (first name, last
name, email address, html/text) from one server and writes
it to a text file. Then a second server imports that
textfile.

Some of the email addresses (10%) are duplicates and I
want to get rid of them. I thought I had it working ok in
dts by setting the second server email field to
unique/primary key... but the problem is that the maximum
errors I can allow for in dts appears to be 9999 and I
have 12000 duplicates. So the import fails, as each row
import that fails (because the key already exists) counts
as an error.

Any workarounds? Better ways to do it?

Thanks for the advice!

Geoff Baker

Whilst I've tried running a package with that many errors, the limit for
max error count is actually 2147483647 since it is defined as a long.
You can set up to this by using the Disconnected Edit feature found on
the Package menu.

The "cleaner" way to do this might be to use an ActiveX Script
transform. Within this you could use a lookup which checks if this is a
duplicate or not. If so it returns an alternative transform status such
as DTSTransformStat_SkipInsert to suppress the error.

A few articles about lookups-

How to Use Lookups in DTS
http://www.sqldts.com/default.aspx?6,107,277,7,7

http://www.databasejournal.com/featu...le.php/1459171
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: newbie duplicate delete dts question

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: newbie duplicate delete dts question - 08-06-2003 , 01:41 PM