Hello

I have used dts, but have not had to write any Activx scripts. I am trying
to update table A from table B if the vendor_number field matches, if it
does not match, I want to insert a new record into table A. Following is
the script I created, it fails with a dup key error.

any help would be greatly appreciated

Jim Wile

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

DTSDestination("VENDOR_NAME") = DTSSource("VENDOR_NAME")
DTSDestination("ADDRESS_1") = DTSSource("SYS_ADDRESS_1")
DTSDestination("ADDRESS_2") = DTSSource("SYS_ADDRESS_2")
DTSDestination("CITY") = DTSSource("SYS_CITY")
DTSDestination("STATE") = DTSSource("SYS_STATE")
DTSDestination("ZIP_CODE") = DTSSource("SYS_POSTAL_CODE")
DTSDestination("PHONE_NUMBER") = DTSSource("PHONE")
DTSDestination("ADDRESS_3") = DTSSource("SYS_ADDRESS_3")
DTSDestination("TAGS_ON_BILLS") = ("N")
If DTSDestination("VENDOR_NUMBER") = DTSSource("VENDOR_NUMBER") Then
Main = DTSTransFormstat_UpdateQuery
Else
Main = DTSTransFormstat_InsertQuery
DTSDestination("VENDOR_NUMBER") = DTSSource("VENDOR_NUMBER")
End If
' Main = DTSTransformStat_OK
End Function

The problem with this is that you are comparing Vendor Number to Vendor
Number. You are doing this One row at a time so there may be matches
but not at that particular juncture.

Do this in TSQL or at least the UPDATE part if you can. It will be much
quicker.



"Jim Wile" <jimwile (AT) mopac (DOT) com> wrote

Thanks for replying,
I could use some help transforming this into a working script., I am just a
novice in using DTS.

Thanks
Jim Wile

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You are going to find it really really slow to do it in this task.

Use an ExecuteSQL task to do your updates by matching on PK columns

You can then use a qualified SourceSQLStatement to do your inserts using
a DataPump task

Allan

"Jim Wile" <jimwile (AT) mopac (DOT) com> wrote