How to Use Global Variable in SQLExecTask SQL Stmt 1

[JohnBates]

hi experts,

I'm using global variables for the first time ever.

I have 1 global var and an input box which stores the user input into the global var. That much works.

Now, in another part of the DTS package, I want to reference the value in an SQL statement... not so easy.

The SELECT stmt is....

FROM dbo.Claims
WHERE DATEDIFF(month,[Proc Date], getdate()) > DTSGlobalVariables("MonthsToKeep").value

It gives an error "function DTSGlobalVariables does not exist" or something similar.

How can I use the gloabl variable value in a Select statement ?

Thanks, John

 

[gradley]

The only way you can access global variables in an Execute SQL task is via the Input/output parameters. Your code should look like the following:

FROM dbo.Claims
WHERE DATEDIFF(month,[Proc Date], getdate()) > ?

Once you specify the "?", you can select the parameters button and point to a Global Variable.

 

[JohnBates]

Thanks gradley !

Wow, it's much easier in 2000 than it was in Version 7.
John