Data Driven Queries 2

[Katy44]

Hi,
I'm trying to set up a data driven query. I have my two connections, a data driven query task and and an activeX script task.
What is the order that these should be in?
I actually want to check whether, for each record from the source, a primary key exists in the destination, then if it does, I want to update, else to insert. Does anyone know of any good examples of this?

 

[gradley]

Try added a lookup to your data driven query. The lookup will perform a select statement on the table you want to test for.

Ex:

Select Any_column
FROM   PK_Table
WHERE  PK_value = ?

In the ActiveX script that moves source to destination, set a variable equal to this lookup value passing the PK_value as a parameter. If the value exists, execute update query. If not execute insert query.

Ex:

Function Main()

Dim strPKValue

	strPKValue = DTSLookups("PK_Test").Execute(DTSSource("Col001"))

	'  If record already exists, update values
	if strPKValue <> "" then
		DTSDestination("First_Name") = DTSSource("Col002")
		DTSDestination("Last_Name") = DTSSource("Col003")
		DTSDestination("Middle_Init") = DTSSource("Col004")
		Main = DTSTransformStat_UpdateQuery

	' else, insert record
	else
		DTSDestination("Assoc_ID") = DTSSource("Col001")
		DTSDestination("First_Name") = DTSSource("Col002")
		DTSDestination("Last_Name") = DTSSource("Col003")
		DTSDestination("Middle_Init") = DTSSource("Col004")
		Main = DTSTransformstat_InsertQuery
	end if
End Function