Hello,

I'm trying to use a global variable in a simple SQL statement in a DTS.
I created a global variable (type integer) at the package level. I
know I can access it in an ActiveX script
(DTSGlobalVariables("myGlobalVariable").Value), but that syntax doesn't
work in the SQL statement.

My SQL is basically this:

SELECT myGlobalVariable = COUNT(*)
FROM myTable

Your consideration is much appreciated.

Eddie

Eddie,

You may want to check this out, especially the paragraph before 'Input &
Output Parameters':

http://www.sqldts.com/default.aspx?234

Lots of other goodies here too. Let us know how you make out.

- Tom

Thomas Bradshaw
MyWebGrocer.com
Data Integration Services

--
Message posted via http://www.sqlmonster.com

Tom,

Thank you for pointing me in that direction. I set up a tiny stored
procedure to run my SQL with an output parameter for my rowcount. I
set up the DTS to call the stored procedure (like at the website you
suggested) and store the returned value and output parameter as a query
result. If I run the SQL from the DTS in Query Analyzer, I get the
correct value in the output parameter. But the value of the global
variable is not changing when I run the DTS, and I did map the global
variable to the output parameter, so I am at a loss as to why I can't
get the global variable to change value.

Still tryin'....

Hello Eddie,

How do you know the value is not changing?

The code is the exact same between QA and DTS?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi Allan,

I've gone over it a few times, as has my manager, and we see no
differences between my code and the sample code at SQLDTS.

After I execute the particular step in the DTS which should change the
value of the global variable, I check it's value by looking at the
Properties of the Package. And it's not changing. The value in the
parameter is correct, but the global variable remains unchanged.
Whatever value I initially set the global variable is it's value
throughout my DTS.

We've searched online and found other instances where people have said
this same code does not work, but we haven't found an alternative
solution yet.

Eddie

Hello Eddie,

Have you checked the case-sensitivity of your Global Variable names so they
all match exactly? For example, if you have a global variable "MaxProductID",
but have an ActiveX task with a lower-case "m", such as:

MsgBox DTSGlobalVariables("maxProductID").Value

You won't see your global variable, but instead create a new one.
Not sure if this matters, but just a thought.

- Tom

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200603/1