MS SQL Server DTS - Skip Rows that Violate Primary Key

Author

Skip Rows that Violate Primary Key

- Steve -

2005-07-19, 8:24 pm

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

Bill Swartz

2005-07-19, 8:24 pm

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@calpoly.edu> wrote in message
news:uy9f4CLjFHA.3300@TK2MSFTNGP10.phx.gbl...
> 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
>

- Steve -

2005-07-20, 3:24 am

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@yahoo.com> wrote in message
news:uXXhDQLjFHA.1444@TK2MSFTNGP10.phx.gbl...
> 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@calpoly.edu> wrote in message
> news:uy9f4CLjFHA.3300@TK2MSFTNGP10.phx.gbl...
>
>

Dieter - via SQLMonster.com

2005-07-20, 7:23 am

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("PriKey"))

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

End Function

function InsertSuccess
'Tell the remote table, that the record could be
inserted
DTSLookups(" LookUpUpdateRemoteTa
ble").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(" LookUpUpdateRemoteTa
ble").Execute "NOK", DTSSource("PriKey")
' Tell the pump everything's alright
InsertFail = DTSTransformStat_OK
end function

peterDavey

2005-07-21, 3:23 am

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@calpoly.edu> wrote in message
news:emoz4POjFHA.1948@TK2MSFTNGP12.phx.gbl...
> 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@yahoo.com> wrote in message
> news:uXXhDQLjFHA.1444@TK2MSFTNGP10.phx.gbl...
on[color=darkred]
although[color=darkr
ed]
rest[color=darkred]
>
>

Sponsored Links