Activex script does not run on scheduled dts package
 

Hi,

I have a dts package which has three global varibles 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 scipt has 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

Result
 

Finally here is the code... It will first fetch the values from database and then assign it to the global varibales and then update the new values to the database...


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

Function Main()

dim ConnSQL1, RSSQL, strSQL, StartTime, Interval, RowDate

'************

set ConnSQL1 = CreateObject("ADODB.Connection") set RSSQL = CreateObject("ADODB.Recordset")

ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=avayacms;UID=sa;Password=Carlson@1"

'************

strSQL = "Select RowDate,StartTime,Interval from Global_Variables"

RSSQL.Open strSQL, ConnSQL1

do until (RSSQL.EOF)

RowDate = RSSQL.Fields(0) StartTime = RSSQL.Fields(1) Interval = RSSQL.Fields(2)

RSSQL.movenext loop

RSSQL.close

'************

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

'************

strSQL = " Update Global_Variables Set RowDate=' " & RowDate & " ', StartTime=' " & StartTime & " ', [Interval] = ' " & Interval & " ' "

ConnSQL1.execute strSQL

ConnSQL1.close

'************

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

Main = DTSTaskExecResult_Success
End Function