July 27, 2011 at 12:04 pm
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
August 2, 2011 at 5:41 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply