DTS activeX script...please help begginner

Hi there, im using this vb script.

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

' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function

what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements) as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:

if record exists in destination
do update statement

if record not exist
do insert startement

many thanks in advance.

You can use an execute SQL task with two statements, one that inserts if the record does not exist and one that updates if the record does exist.

-Sue

|||Thanks for your reply, could you please give me a code example of this.

Many thanks|||

thanks for that. I've abandoned the activeX and have decided to use an execute SQL task using this tsql:

INSERT INTO dbo.Target
SELECT *
FROM dbo.Source AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Target AS t
WHERE t.title_id = s.title_id
)

however my source and target tables are on different database, so when I choose a connection in the execute sql task I get an error as each database table exists different database, how could I over come this?

many thanks

|||

Im using this activeX vb script to perform updates or inserts depending if the primary key already exists in the destination table, however the it never updates when it should, infact it never updates it always inserts, which leads to a violation of the primary key. Can somebody help me see where Im going wrong...thanks

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

' Copy each source column to the destination column
Function Main()
Dim sSessCode
sSessCode = DTSDestination("SES_ID")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
If DTSSource("Start Date") = "01/01/1753" Then
DTSDestination("SES_DATE") = "01/01/1980"
Else
DTSDestination("SES_DATE") = Cdate(DTSSource("Start Date"))
End If
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SLC_ID") = DTSSource("Sub Location")

DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("SES_STATUS") = DTSSource("Session Status")

Select Case sSessCode
Case DTSSource("Session Code")
' MsgBox("Update")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_UpdateQuery
Case Else
' MsgBox("Insert")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_InsertQuery
End Select
End Function