I'm just getting my feet wet with DTS and was asked to perform a
function which is beyond my knowledge at this point. I'm trying to run
Data Drive Task in DTS which will view the DTSSource.id within the
DTSLookup and check it against the DTSDestination.id. If there is no
entry of the id in the Destination, I'd like an insert to happen of all
the fields in the DTSSource but if the id exists then to only update 3
of the 5 fields in the Destination table. I'm using an ActiveX script
on the Transformations tab and have linked all my fields in a single
transformation. My Insert seems to work beautifully but my update
fails. I'll run the DTS package the first time and it inserts
everything but when I run it the second time the updates fail with the
error 'Error converting data type varchar to numeric. I believe that
the update is trying to take my first field which is the id (numeric)
and put it into the user_name field. My scripts are as follows:
************************************************** ****
ActiveX Transformation Properties
************************************************** ****
Function Main()
DTSDestination("id") = DTSSource("ID")
DTSDestination("user_name") = DTSSource("LOGIN")
DTSDestination("first_name") = DTSSource("FIRST_NAME")
DTSDestination("last_name") = DTSSource("LAST_NAME")
DTSDestination("password") = DTSSource("PASSWORD")
DTSDestination("role_ids") = DTSSource("ROLE")

If IsEmpty(DTSLookups("IDLookup").Execute(DTSSource(" ID").Value) )
Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformstat_UpdateQuery
End If
End Function
************************************************** *****
Insert Statement
************************************************** *****
INSERT INTO User_Accounts
(User_Accounts.id, User_Accounts.user_name,
User_Accounts.first_name, User_Accounts.last_name,
User_Accounts.password, User_Accounts.role_ids)
VALUES(?,?,?,?,?,?)

************************************************** ******
Update Statement
************************************************** ******
UPDATE User_Accounts
SET user_name = ?, first_name = ?, last_name = ?

WHERE (id = ?)
************************************************** ******
Lookup Statement
************************************************** ******
SELECT id
FROM User_Accounts
WHERE (id = ?)
************************************************** ******
How do I pass the fields of id and password when I run an update? I
haven't found anything in the Google groups or in my SQL Server DTS
book to resolve this issue.

Thanks for any help you could offer.

Sue