Hello

I hace creatre a dts package that uses an ActiveX script to either insert or
update existing records in an Oracle RDB database. The script fails on an
duplicate key value on the index on the vendor_number field. The
Vendor_number field is the field I am using in a lookup (which works) to
verify a matching record in the output database(Oacle RDB). Please review
the following ActiveX script and advise me of any corrections.

Thanks

Jim Wile
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DIM Lookup_Vendor
DIM InsertOrUpdate
stop
Lookup_Vendor =
DTSLookups("Lookup_existing").Execute(DTSSource("S HORT_VENDOR_NUMBER"))
If (IsEmpty(Lookup_Vendor)) Then
Main = DTSTransFormstat_InsertQuery
MSGBOX ("insert " & Lookup_Vendor)
InsertOrUpdate = "Insert"
Else
Main = DTSTransFormstat_UpdateQuery
MSGBOX ("update " & Lookup_Vendor)
InsertOrUpdate = "Update"
End If
If (InsertOrUpdate = "Insert") Then
DTSDestination("VENDOR_NUMBER") = DTSSource("SHORT_VENDOR_NUMBER")
End If
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("VENDOR_TYPE") = ("XX")
DTSDestination("TAGS_ON_BILLS") = ("N")
Main = DTSTransformStat_OK
End Function

Is it possible you have duplicates in the source?

Remember that these rows do not necessarily get inserted straight away
either as the default I to commit the whole batch at once.



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

Hi Allen

No, I checked, there are no duplicate records in the input source, any other
thoughts?

Thanks
Jim Wile


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

Is there no one who can help me with this problem, I have been struggling
with it for days.

Thanks
Jim Wile


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

Sholdnt the last Main = DTSTransformStat_OK be either
DTSTransFormstat_InsertQuery or DTSTransFormstat_UpdateQuery ?

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

Paul thanks for responding,

I commented out the last line (Main = DTSTransformStat_OK) and receive the
following error:
'DataDrivenQuery : Transformation status returned a DataDrivenQuery value,
but no DataDrivenQueries were specified'

This is my first ActiveX script and I am lost! any help would be greatly
appreciated.

Thanks
Jim Wile


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

This is in a DDQ (Data Driven Query Task) right?

If it is then have you defined the queries for the actions you want to take?

Itzik Ben-Gan has an excellent article on using the DDQ in SQL Server magazine.

Personally I would look to throw the rows over to oracle and into a stagin table. I would then use PLSQL to do the INSERT/UPDATE.
It is invariably much quicker.




--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


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

I, too, have an article with code:

http://msdn.microsoft.com/library/de...ml/sql00l5.asp

;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

This is in a DDQ (Data Driven Query Task) right?

If it is then have you defined the queries for the actions you want to take?

Itzik Ben-Gan has an excellent article on using the DDQ in SQL Server
magazine.

Personally I would look to throw the rows over to oracle and into a stagin
table. I would then use PLSQL to do the INSERT/UPDATE.
It is invariably much quicker.




--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


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

And Tom has an article also.....


Sorry Tom.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

;-)


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

And Tom has an article also.....


Sorry Tom.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote