multi phase data pump example

#1

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

Quote:

-----Original Message-----
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 in
message
news:70b801c4022c$ae509630$a001280a (AT) phx (DOT) gbl...
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

.

..

#2

Check the FAST LOAD as well as changing your handlers to

Function InsertFailureMain()
InsertFailureMain = DTSTransformStat_OK
End Function

---------

Transform Failure Function

Function TransFailureMain()
TransFailureMain = DTSTransformStat_OK
End Function

--

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

Quote:

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

-----Original Message-----
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 in
message
news:70b801c4022c$ae509630$a001280a (AT) phx (DOT) gbl...
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

.

.

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

multi phase data pump example

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

multi phase data pump example - 03-04-2004 , 08:31 PM

Re: multi phase data pump example - 03-05-2004 , 12:26 AM