Problem in executing the Activex script on DTS Package

Are you expecting the end values of the variables to be used as the starting values the next time you run the package?

If so, you'll have to save them to a table or file and read them back in each time you run the package.

Variable state is not persisted between DTS executions. What you hard-code at design time is always used as the starting value.

[Edit for codes request]

I don't have a SQL 2000 instance to get the task names, but you need to...

• Create a 3 column table to hold your global variable values
• Use a query in DTS (map the variables to the query outputs) to read the table values into your global variables ... this step will be immediately prior to your existing active X task
• Use/modify the global variables in your existing active x task (you're already doing this)
• Immediately after your active X task, use another query in DTS (map the global variables to the query inputs) to write the newly updated variables back to the table

Unfortunately, there isn't really much "code" to show you for this, just some DTS query tasks you'll need to add.

[/Edit]

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

'msgbox DTSGlobalVariables("StartTime").value Main = DTSTaskExecResult_Success End Function

Is it possible that you are escaping the function before doing the actual work? Try moving the DTSTaskExecResult_Success

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

' Visual Basic ActiveX Script

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

Function Main()

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

Main = DTSTaskExecResult_Success

End Function