Using Global Variables in DTS

Question

Post reply

Using Global Variables in DTS

Rand Hammack

SSC Journeyman

Points: 84
More actions
September 3, 2004 at 12:25 pm

#59317

Any help would be appreciated.
I am creating a DTS package with several steps. My problem involves two steps, an ActiveX and an SQL task.
I have a statement within the ActiveX task:
If oFile.Size > 14 Then
   Main = DTSTaskExecResult_Success
  Else
  Main = DTSTaskExecResult_Failure
End If
I thought this would set the workflow to either success or failure, it always succeeds. I want different two different SQL task to perform depending on the results.
OR
I can change my two SQL task to one. If, by populating a global vairable within my ActiveX script. But, I have trouble referencing a global variable within the SQL task. Does anyone have an example of how to use a global variable in a SQL task?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Atiq Rahman SSC Journeyman Points: 98 More actions · Answer 1

Hi,

Others may have the better way to work with Global Variables, but I do it like this:

First Create the Global Variables in your package.

In SQL Task write your query,

eg. "select count(*) CNT from EMPLOYEES".

In parameters go to Ourput Parameters and select output parameter type as Row Value and do the patameter mapping.

This way you can assign the count of the EMPLOYEES table in a variable. And this can be used in ActiveX scsript.

In ActiveX Task Properties in Browser Tab under Global Variables you will find the list of variables declared in your package.

Regards,

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

Just a thought, but is your file always bigger than 14? have you tried setting that number higher, or even reversing the test to

if oFile.Size < -1 then

to make it fail?

Global Variables are a pain to use, but then again, so is DTS most of the time.

Julian Kuiters
juliankuiters.id.au

Rand Hammack SSC Journeyman Points: 84 More actions · Answer 3

Thanks for all the help from everyone.

I found a .dll that populates my Query Task with the global variables automatically each time it runs.

If anyone else is having the same problem with using Global Variables outside of an Active X script. Search for GVCustomTask at http://www.databasejournal.com

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 4

I don't know what version you're using, but the DTS package process in SQL2000 is far ahead of SQL7. Global variables are fairly straightfoward, and we have transferred all of our import that used to done by VB programs to DTS packages. The results have been fantastic. Better, more reliable, and faster. I strongly encourage that DBAs use and learn them.