DTS global variable - SQL Server Forums

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)

DTS global variable

Author

Topic

inonli
Starting Member

6 Posts

Posted - 2003-06-30 : 09:00:25

Hello all
what is the way to access DTS global variable from stored procedure or any other SQL in MSSQL97. (no problem in 2000 with parameters inside the SQL task in DTS or DTSRUN /A option. i cant find the answer on 97
Thx
Inonli

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-30 : 19:38:35

uuuhhhhh...do you mean SQL Server 7?

Can't be access 97 can it? No DTS there.

Why don't you just use a stored procedure.

What are you trying to do?

I'm sure the ladies and gentlemen here can give you some advice (some of the are even MVY's)

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-30 : 19:41:04

Global variables do not exist in versions prior to SQL Server 2000. And yes what do you mean by 97?

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-05 : 07:22:29

Not exactly so. I've just now found out this trick.
(it was tested on SQL Server 7.0, no Service Packs)

'********************************
' Visual Basic ActiveX Script
'********************************
Function Main()
MsgBox DTSGlobalVariables("myGBV").Value

' myGBV is your global variable defined in 'Global Variables' tab of
' your package properties, in my case its data type is 'String'

Main = DTSTaskExecResult_Success
End Function

Then,
in your VBA(VBS) client's code you can assign new value to this global
variable (e.g., new path to a file) and execute the package:

Dim pck As New DTS.Package
pck.LoadFromSQLServer "myServer", "user", "pwd", , , , , "myPackage"
pck.GlobalVariables("myGBV") = "Hi there!!"
pck.Execute
pck.UnInitialize
Set pck = Nothing

- Vit

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-05 : 11:29:17

This works on v7 and 2000.

http://www.nigelrivett.net/SetDTSRunTimeValues.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources