Lookup with DTS & VB Script

[DaveRolph]

Following on from a previous post(

http://www.tek-tips.com/viewthread.cfm?qid=1138954&page=1

)

This is my first experience using VBS with DTS, I have the following Scipt within my DTS Package.


Dim tSeq
Dim tCleanSeq

Function Main()
tSeq = tSeq + 1
tCleanSeq = Right("0000000" & tSeq, 8)
DTSDestination("Patient_Number") = DTSSource("User.Ref")
DTSDestination("Details") = DTSSource("NOTE-DESC")
DTSDestination("Entered_By") = DTSSource("GN-AUTHOR")
DTSDestination("Entered_Date") = DTSSource("GN-DATE")
DTSDestination("Gen_Notes_ID") = tCleanSeq
DTSDestination("From_DB") = 3
Main = DTSTransformStat_OK
End Function



I then have a TSQL Script to update a column (one of those in the destination table), I am sure this could be done within the script rather than at the end of the import.

But I have no idea how, any idea's ?

Thanks

BTW This is the TSQL Update Script...

Update GENERAL_NOTES Set GENERAL_NOTES.Patient_Number = PATIENT_NUMBERS.Patient_Number,GENERAL_NOTES.Patient_No_Full= PATIENT_NUMBERS.Centre_Code+PATIENT_NUMBERS.Patient_Number
FROM GENERAL_NOTES INNER JOIN PATIENT_NUMBERS ON GENERAL_NOTES.Patient_Number = PATIENT_NUMBERS.Old_Code