DTS VB Code help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS VB Code help

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-11-07 : 10:11:50

I have a DTS that looks into one database (sqlbase) and then compares it to another database (sqlserver) for employee that exists or not. If the employee exists it just dumps all info to the sqlserver database according to the employee id and if it does not exists it will insert new employee from sqlbase to sqlserver database. The update part works, however my insert section does not work. Can someone look at my VB script and see if they can find the flaw in there?

The part in bold might be throwing me off. It is supposed to check for the employee id and see if it exists in sqlserver or not. If it does not exists it will insert the new records, otherwise update.


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

'  Copy each source column to the destination column
Function Main()
	DTSDestination("BASE_PAY_RATE") = DTSSource("RATE1AMT")
	DTSDestination("SHIFT_ID") = DTSSource("DATACONTROL")
	DTSDestination("DEPARTMENT_ID") = DTSLookups("Dept").Execute(DTSSource("HOMEDEPARTMENT"))
	DTSDestination("FIRST_NAME") = DTSSource("FIRSTNAME")
	DTSDestination("LAST_NAME") = DTSSource("LASTNAME")
	DTSDestination("ID") = DTSSource("FILE#")

	If IsNull(DTSLookups("EmpActive").Execute(DTSSource("FILE#"))) Then
		Main = DTSTransformStat_InsertQuery
	Else
		Main = DTSTransformStat_UpdateQuery
	End If	
	
	If  DTSSource("DATACONTROL") = 2 Then
		DTSDestination("BASE_PAY_RATE") = CStr(DTSSource("RATE1AMT"))  + .80
	End If

End Function

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-11-07 : 11:08:13

Got it working now. I just realize that when it checks for that Null value, since the employee record for that ID never ever existed, that field can't be NULL but empty. So it's always going to be false and will not insert it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources