Problem in executing the Activex script on DTS Package

See more:

SQL-Server

I have a dts package in Sql server 2000 which has three global variables in it. The script run as expected when i run it manually. But when i close the dts package and then run it by right clicking it or when i schedule the package.. the variables wont update. I have checked the permission and security of sql server agent and the user.. from which i run the script.. all admin rights.

Please help...

Below is the script

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

RowDate = DTSGlobalVariables("RowDate").value
StartTime = DTSGlobalVariables("StartTime").value
Interval = DTSGlobalVariables("Interval").value

IF StartTime = 2400 Then
StartTime = 0
RowDate = DateAdd("d" , 1 , RowDate)

ElseIF Interval = 30 Then
StartTime = StartTime + 30
Interval = 70

ElseIF Interval = 70 Then
StartTime = StartTime + 70
Interval = 30

End IF

DTSGlobalVariables("RowDate").value = RowDate
DTSGlobalVariables("StartTime").value = StartTime
DTSGlobalVariables("Interval").value = Interval

End Function