I'm importing data from an Oracle database to my SQL box using DTS.

We have a column called GUID. In oracle it's not a primary key, although
it's supposed to be (out of my control), in SQL I've made it a primary key.

Every now and then we get duplicate GUID's in Oracle. Then my package fails
becuase of this one record. How can I keep DTS importing the rest of the
rows and skipping the one bad row?

Steve

If you enable the multi-phase datapump, you can specify actions to take on
"On Insert Failure".

You enable the by Right Clicking "Data Transformation Services", then
properties.

Bill

"- Steve -" <scevans (AT) calpoly (DOT) edu> wrote

Okay I figured out how to enable the multi-phase datapump, and chekced the
"On Insert Failure", but I don't seem to be able to figure out what to do
beyond that.

I can get it so there's no error, but then no data ends up in the
destination table (although it counts through all the records when you
execute), and I can get it to remain reporting the error and not importing
any rows, but I can't produce the end result I need.

Thanks,
Steve

"Bill Swartz" <wsaharem (AT) yahoo (DOT) com> wrote

Here we go:

' Transform
Function Main()

dim res
DTSDestination("col1") = DTSSource("col1")
res = ""
'Have a look if the PriKey exists local
'Lookup returns the PriKey if exists
res = DTSLookups("SomeLookUpLocal").Execute(DTSSource("P riKey"))

if (res = "") then
'Do da insert
Main = DTSTransformstat_InsertQuery
else
'Tell the remote table, that the
record could NOT be inserted
DTSLookups("LookUpUpdateRemoteTable").Execute "NOK", DTSSource("PriKey")
'Skip this, cause it's already here
Main =DTSTransformStat_SkipRow
end if

End Function


function InsertSuccess
'Tell the remote table, that the record could be
inserted
DTSLookups("LookUpUpdateRemoteTable").Execute "OK", DTSSource("PriKey")
' Tell the pump everything's alright
InsertSuccess = DTSTransformStat_OK
end function

function InsertFail
'Tell the remote table, that the record could not be
inserted
DTSLookups("LookUpUpdateRemoteTable").Execute "NOK", DTSSource("PriKey")
' Tell the pump everything's alright
InsertFail = DTSTransformStat_OK
end function

Steve,
In the Options tab of the Transform Data Task dialogue:

Uncheck 'Use Fast Load'
Set max error count to a figure that you think will be more the rejected
rows.
Check:
Error text
Source error rows
Dest error rows
Define an exception file name.

The task will continue to run as long as the max errors isn't exceeded. The
rejected rows will be written to a file with the same name as the exception
file but with .dest or .source appended to the end.

Good luck

cheers
peterDavey
Melbourne


"- Steve -" <scevans (AT) calpoly (DOT) edu> wrote