Activex script does not run on scheduled dts packa

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Activex script does not run on scheduled dts packa

Author

Topic

progzr
Starting Member

4 Posts

Posted - 2011-07-25 : 16:48:35

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-25 : 17:11:42

Are you logging execution to a file? What does the log show?

progzr
Starting Member

4 Posts

Posted - 2011-07-26 : 15:49:22

The package executes successfully.. But global varibles does not update.....

Please help..... searching the answer since 3 days.....

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-26 : 16:29:35

See above post

How do you know the variables aren't updating?
Do you know that they already have values before this script runs?

progzr
Starting Member

4 Posts

Posted - 2011-07-27 : 09:54:34

They are global varibales and already have values. the problem is when i close the package and then execute it by right clcicking it.. then they wont update but when i open the pakage and then run the script the varibales get updated.....

progzr
Starting Member

4 Posts

Posted - 2011-08-02 : 07:36:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources