I am working on a SQL 2000 DTS job that will look at a flat file, and then import the information from the flat file to our SQL DB. However, it must first check each record to see if it already exists. If it does, then it must update the record rather than insert a new one.
I am using a Data Driven Query for this. Here is the code for my ActiveX transformation script:
Function Main()
DTSDestination(“hblID”) = DTSSource(“hblID”)
DTSDestination(“hblNum”) = DTSSource(“hblNum”)
DTSDestination(“mblNum”) = DTSSource(“mblNum”)
DTSDestination(“pol”) = DTSSource(“pol”)
DTSDestination(“pod”) = DTSSource(“pod”)
DTSDestination(“portLoadingDate”) = DTSSource(“portLoadingDate”)
DTSDestination(“estArrDate”) = DTSSource(“estArrDate”)
DTSDestination(“vessel”) = DTSSource(“vessel”)
DTSDestination(“voyage”) = DTSSource(“voyage”)
DTSDestination(“shipper”) = DTSSource(“shipper”)
DTSDestination(“consignee”) = DTSSource(“consignee”)
DTSDestination(“contLoadType”) = DTSSource(“contLoadType”)
DTSDestination(“contNum”) = DTSSource(“contNum”)
DTSDestination(“seal1”) = DTSSource(“seal1”)
DTSDestination(“cartons”) = DTSSource(“cartons”)
DTSDestination(“weight”) = DTSSource(“weight”)
DTSDestination(“cbm”) = DTSSource(“cbm”)
DTSDestination(“hsNum”) = DTSSource(“hsNum”)
DTSDestination(“description”) = DTSSource(“description”)
DTSDestination(“COL020”) = DTSSource(“Col020”)
Select Case DTSSource(“hblID”)
Case DTSDestination(“hblID”) Main = DTSTransformstat_UpdateQuery
Case Else
Main = DTSTransformstat_InsertQuery
End Select
End Function
However, it never actually updates duplicates, it just inserts them again. I have noticed that if I put a hard code value intomy case statement, it works properly. So obviously, I am just not comparing properly.
Any help on this would be greatly appreciated. This is my first time doing a DDQ so I am a
