I am trying to consolidate some code that I had replicated in many places in
my DTS package by placing it in a Dynamic Property that sets a global
variable. I can reference this variable through ActiveX Scripts with no
problem. However, I can not figure out how to access it in an SQL query in
another Dynamic Property that sets a source file path. I tried to prefix the
global variable name with @ and @@, but both times I was told that I needed
to declare the variable. How can I access this variable so I don't have to
keep a whole lot of SQL in this dynamic property?

Thanks in advance.

--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Store it in a table


"Chris Lieb" wrote:

Hi Chris
Not sure if I'm undertanding your question correctly but within a DTS
package I set some global variables which I subsequently reference with ? in
SQL tasks.
You have to declare these as input parameters on the task. Below is an
example of a query we use to reference four global input paramters.


SELECT *
FROM LatestMWhStn
WHERE
ModDtTm >= ? AND
ModDtTm < ? AND
NZDTDate BETWEEN ? AND ?

Hope this is what you need.

Thanks
Pete



"Chris Lieb" wrote:

Hi Chris,

Chris Lieb wrote:
in a sql task you have to use ? as placeholder for global variables and then press the parameter button to select the corresponding
variables.

But you can't do this in an sql query of the dynamic property task. So you could use another sql query to get the result stored as
global variable via output, and then reference this in the dynamic property task directly.

Helge

Once you have assigned the value to a global variable through the DP task
then you can use it in a number of places.

The ExecuteSQL task, the DataPump task and the DataDriveQuery task all
support ? as a placeholder for queries where the ? will be derived from a
Global Variable at runtime.

Not all drivers support ? though so be careful against what type of source
you use this.

In places where you are not able to use ? then you can build the statement
at runtime and populate by using, as you have mentioned, an Active X Script
task.

There are examples of doing this at www.SQLDTS.com

Allan

"Chris Lieb" wrote: