Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Introduction To Global Variables
By Allan Mitchell
Version 7.0/2000
Level Beginner
Page 1 | Page 2 | Page 3

Not declaring local variables in DTS can lead to DTS creating them on the fly. The same applies to Global Variables. You will notice at the bottom of the screen in Figure 1.2 that we have a checkbox labelled Explicit Global Variables. This is to Global Variables what Option Explicit is to local variables. It means that should we misspell the name of our Global Variable then we are alerted to the fact. Now here's the bad news for users of SQL Server 7. There is NO way of stopping SQL Server from creating Global Variables on the fly. Sorry. You just have to be careful because as we show in the tutorial on using ActiveX there is no browser window for you either. Look at the following code to see that what shouldn't work does and then look at how we fix it.

Function Main()

	Dim ThisIsCorrect

	Msgbox ThisIsWrong

	Main = DTSTaskExecResult_Success
End Function

When we execute the script it still works.

Figure 1.3 Actually Works.

AtuallyWorks

Simply adding the following line to the top of the code (outside the function) will cause it to fail as we expect.

Option Explicit

Figure 1.4 Now it Fails.

NowItFails

Tip:  So you don't get caught out by this when you want to use a Global 
Variable in 2000 just double click on it from the browser window. 
DTS will add it to the script window with the correct spelling.

Conclusion

As you can see Global Variables can be extremely powerful and they add flexibility to your packages. There are many examples on this site of using them.

Page 1 | Page 2 | Page 3