x

Problem in executing the Activex script on DTS Package

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
more ▼

asked Jul 27, 2011 at 07:34 AM in Default

progzr gravatar image

progzr
11 1 1 1

Hi Kenj,

Thnaks for the help...Can you please give a code to do this..
Jul 28, 2011 at 01:58 AM progzr
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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]
more ▼

answered Jul 27, 2011 at 08:53 AM

KenJ gravatar image

KenJ
19.3k 1 3 11

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jul 28, 2011 at 09:07 AM

SirSQL gravatar image

SirSQL
4.8k 1 3

Tried but the results are same.. No change in global variables..........
Jul 28, 2011 at 09:39 AM progzr
Wait, how are you viewing the global variables? Are you outputting them to a table or file somewhere?
Jul 28, 2011 at 02:31 PM SirSQL
No. From the package itself... They retain their values...
Jul 29, 2011 at 06:35 AM progzr

Any changes to global variables in the package are only saved when the package itself is saved. This means that if you run the package through a job then the variables will be assigned as directed however they will not be saved in the package itself. If you want to confirm that they are being set as you wanted you would need to output them to a table or file somewhere for checking.

At this point I don't think it's your script that's causing you the problem, rather it's a change to your validation method that might well help you get past this.
Jul 29, 2011 at 09:15 AM SirSQL

Thanks a lot sir, I checked once again and that's true.. script is working properly and also supply variables to parametrized query as expected... global varibles wont save unless its open... So we have to store them into some table and then read values from it....

Sir, Do you have the code for doing so. i.e write values into table and then read back from that table via activex script...

Thanks a lot once again...
Aug 01, 2011 at 10:54 AM progzr
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 02, 2011 at 01:41 AM

progzr gravatar image

progzr
11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66
x27
x20
x3

asked: Jul 27, 2011 at 07:34 AM

Seen: 1638 times

Last Updated: Jul 27, 2011 at 08:53 AM