I would like to automate my DTS package further. I would
like to be able to enter one paramter that would be used
by all the package elements (I'm assuming this would be a
global variable?). The package elements are mostly sql
type. I want the parameter data to be affect table names
for creation and processing in all of the sql
statements. I've tried to find info on the Glob al
Variables but haven't figured out how to use them in this
way. Can you send me in the right direction on this
quest?

Thanks in advance,
B.

In article <055001c3c3e4$7b8ffe60$a001280a (AT) phx (DOT) gbl>, BEE
<anonymous (AT) discussions (DOT) microsoft.com> writes
A global variable would be a sensible place to store data used by any
task in a package, although not all tasks support global variables
directly.

The Exec SQL Task can use a global variable value, but the rules that
goveren what can be a variable are pretty much the same as those for
normal T-SQL and local variables.

In DTS you could use

SELECT * FROM Table WHERE Column1 = ?

The ? is the place holder for the global variable parameter. Click the
Parameters button in the task to map the variable.

This will not work-
SELECT * FROM ? WHERE Column = 1

In the same way this does not work in normal T-SQL
SELECT & FROM @Tableame WHERE Column = 1

So in T-SQL where ypu would work around this by using dynamic SQL, in
DTS you put the dynamic logic into an ActiveX Script Task instead-

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

You can use the object model to set the value of a Global Variable in VB or
C# or whatever your preference for language

In your package you assign this global variable to properties in your
package.

Or cut out the middleman and if you are using the object model in VB etc
then set all properties through this.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"BEE" <anonymous (AT) discussions (DOT) microsoft.com> wrote