DTS Inert With ActveX For Duplicates Check - HELP !

[DaveRolph]

I have been getting nowhere with this one, please can anyone help, NPC_Code is the PK in a table called STOCK

I have a Lookup

SELECT     NPC_Code
FROM         STOCK
WHERE     (NPC_Code = ?)

And Then Trying to use this within the script

Function Main()
Dim tNPC

           tNPC = DTSLookups("luNPC").Execute(DTSSource("NPC_Code").value)

            If IsEmpty(tNPC) Then

	DTSDestination("NPC_Code") = DTSSource("NPC_Code")
	DTSDestination("Annual_Maintenance") = DTSSource("Annual_Maintenance")

	 	Main = DTSTransformStat_OK
	Else
		Main = DTSTransformStat_SkipRow
End if
End Function

The trouble is there IS a duplicate NPC Code and therefore it fails on Unique Contraints !

Help !

 

[SQLDenis]

Why don't you check if it exists before inserting?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DaveRolph]

Wow That was a quick response.

But I thought thats what I WAS Doing, could you please give me an example or more information

Many Thanks

Dave

 

[SQLDenis]

try changing If IsEmpty(tNPC) Then
to
If IsNull(tNPC) Then

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DaveRolph]

What a complete star you are - I have been on this for ages.

Top Man works spot on now ;-)

 

[DaveRolph]

Oh Nooooo

I was wrong, it hasn't worked.

I have zero records in my Table.

Help !

 

[JBaileys]

Have you tried to execute this through straight SQL and pull DTS out of this mix?

Can you consider changing the condition for If IsNull(tNPC) or tNPC = 0 Then ...

 

[DaveRolph]

I am not sure how you mean by Taking DTS out of the mix, other than creating a holding table ? I really wanted to do this in one quick hit.

I DID change the condition.

I am pretty sure this and the other post is related I think I am doing something wrong on the lookup (on the connection)

Basically I am creating a New COnnection for the Lookup is this correct ?

 

[DaveRolph]

At Last

I got this method working.
What this does is enters address type records where they do not already exist in the destination table.

THE LOOKUP TAB HAS THE FOLLOWING

SELECT     Address_Type_Code
FROM         ADDRESS_TYPE
WHERE     (Address_Type_Code = ?)

THE ACTIVE X SCRIPT HAS THE FOLLOWING

'**********************************************************************
' Enter non-Duplicated
' Written 29/01/2006 - DLR - support@softopts.co.uk
'************************************************************************
Dim tExists

Function Main()
	tExists=DTSLookups("Exists").Execute(DTSSource("Address_Type_Code"))

	if (tExists = "") Then	
		DTSDestination("Address_Type_Code") = DTSSource("Address_Type_Code")
		DTSDestination("Address_Type_Desc") = DTSSource("Address_Type_Desc")
		Main = DTSTransformStat_OK
 	Else
		Main = DTSTransformStat_SkipRow
	End if
End Function