Hi:

I am new to SQL server. I need to import data to my SQL database
periodically. The external data may contains duplicated data to my database.
I was using "Import data" initially but it will fail if there is a violation
of PRIMARY KEY constraint.

Then I discoverred this DTS package which allow me to customise the data
transfer process. But I still facing problem of "Violation of PRIMARY KEY
constraint" due to the duplicated data. How can I skip the duplicated data
and proceed to transfer the the rest of the non-error data?

Thank you.

Chin Lin

Hello Chin,

I think that the best way could be:

Step1: Using datapump you could first load all data in a staging-table
Step2: Using an ExexuteSQLTask you could insert from staging-table into your
historical-table only those records not in conflict with yuor PK.

else you could simply try this:

in your datapump , on the Tab 'Options':
--- set the property 'Insert batch size ' = 1,
--- check 'use fast load' (should be the default condition)
--- give a proper value to 'Max error count' (this should be greater than
the numbers of errors you expect to derive from PK conflict). If you use
disconnected edit to set this last property, you have the possibility to
set a value even grater than 9999 but I'm not sure if the maximum number
of errors accepted could be really greater then 9999.

Let me know if it works.

Bye

Davide Rossetti

I always import data to a staging table, then use a stored procedure
to add the data to the production table. It is much easier to handle
any issues such as you have in SQL code than in DTS.

Roy Harvey
Beacon Falls, CT

On Wed, 9 Aug 2006 19:37:01 -0700, Chin Lin <Chin
Lin (AT) discussions (DOT) microsoft.com> wrote:

Hello Chin,

You shouldn't be trying to "cheat" the PK violation. The reason you are
getting it is because you are breaking the rule that the DB designer designated
on the column.

You should be scrubbing your data first to try and mitigate these violations
not throw the data at the table and ask it not to blow.

Stage the data first because this is DTS (Not needed so much in SSIS) and
then do your scrubbing from there. It will be much cleaner this way as you
can also capture and report the rows that are duplicates.

Allan