We have a Nightly Job,

A sp which has an RPC call for getting the data and
Inserts into table A.it runs fine but ocassionly sometimes
Job fails becos of Primary key violation.
So my system has data as of Yesterdays not of latest.

But I read some where its possible to Insert all the
records except the error out record through DTS.

Can any body throw some light on it..

Thanks in Advance
Raj

A Primary key violation will abort the batch but you may want to look at
this

Multiphase Data Pump (Field Notes)
(http://www.sqldts.com/default.aspx?282,1)



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Raj" <anonymous (AT) discussions (DOT) microsoft.com> wrote

ON DATABASE 1

create table sample1
(
c1 int not null primary key,
c2 int
)
go

insert into sample1 values(1,1)
insert into sample1 values(2,1)
insert into sample1 values(3,1)

go

select * from sample1

ON DATA BASE 2


create table sample1
(
c1 int not null primary key,
c2 int
)
go

insert into sample1 values(1,1)
go
insert into sample1 values(2,1)
go

select * from sample1


I had already selected MULTI DATA BASE PUMP Option.

In DTS Designer I established two OLE DB connections are
established for each DATA BASE
I added a Data Transform Task in between connections for
inserting the data from DATABASE 1 to DATABASE 2.

If I start executing this package it is errored out due to
PRIMARY KEY violation.

My aim is by DTS package should executing by adding the
3rd record from the DATABASE1 TO DATABASE2
i.e., after executing both the tables should have the Same
data.



Allen , posted this link this morning.

http://www.sqldts.com/default.aspx?282,1

It helped me a lot .. But still i could not able to
proceed.

I think I am missing something

so, please can any of you help how to proceed or any
example.



Thanks in Advance.

Raj