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;
[email protected]"
'************
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