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

OK I just built a package around what you want and followed my example

I used your scripts

You must

1. Turn off FAST LOAD
2. Handle the "Insert Failure" phase
3. Handle the Transform Failure

It works.



--

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

Allen,

Sorry to Bug you once again.

I added the following transformation in my code

Row Transform Function
' Copy each source column to the destination column
Function Main()
DTSDestination("c1") = DTSSource("c1")
DTSDestination("c2") = DTSSource("c2")
Main = DTSTransformStat_OK
End Function

---------

Insert Failure Function

Function InsertFailureMain()
InsertFailureMain = DTSTransformStat_SkipInsert
End Function

---------

Transform Failure Function

Function TransFailureMain()
TransFailureMain = DTSTransformStat_SkipInsert
End Function

---------


' Insert Success Function

Function InsertSuccessMain()
InsertSuccessMain = DTSTransformstat_OK
End Function

after that I executed the Package But I could able to
succeed Inserting the 3 rd record.

recap:

DATABASE 1 contains 3 records
DATABASE 2 contains 2 records( same as DATABASE 1 records)

Now after executing the Package.

Package should skip the 2 records which already exists.
But should Insert only the 3rd record..


Thanks in Advance.
Raj