I'm creating a new Transform Data Task in SQL Server 2000. I have the
option to select a table/view from the source database, or write my own SQL
to select the appropriate data. I would like to be able to write my own SQL
query, but be able to use the value of a global variable in the query itself
(ad-hoc).

I have an ActiveX Script task as follows (it runs before anything else):
__________________________________________________ _
'Creating an XML object to read the XML
Set xmlDoc=CreateObject("Microsoft.XMLDOM")
xmlDoc.async="false"

'Loading the config file (XML)
xmlDoc.load("\\server\share$\config.xml")

'Getting the term node from the config file (There is only one node labled
"SEMESTER")
Set Nodes = xmlDoc.getElementsByTagName("SEMESTER")

For Each node in Nodes
DTSGlobalVariables("TERM").value = UCASE(node.text)
Next

'Cleaning up the garbage
Set xmlDoc = nothing
Set Nodes = nothing
__________________________________________________ __

From my understanding, this stores the value in a global variable named
"TERM"

The SQL query I would like to use is something like:
Select * From Course_Catalog where term =' <value of global variable>'

My questions are (1) Is it even possible to use the value of the global
variable in the query and (2) Is there better way to accomplish this?

Thanks.

In message <#kBENgJCFHA.936 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, news.microsoft.com
<trsharpe (AT) holly (DOT) colostate.edu> writes
If you want to store config in Xml, then that's cool. Using the ActiveX
Script Task to set the values to global variables makes sense. So now to
using them. The SQL would look like this-

Select * From Course_Catalog where term = ?


The ? is a parameter place holder. Click the parameters button in the
SQL task, and you can then see 1 parameter listed, as you have one ?, so
select the global variable you want to be that value.


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

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

Thanks for the help. I've tried to use parameters but when I click on the
parameters button, I get the following error message:
______________
Error Source: Microsoft OLE DB Provider for ODBC Drivers

Error Description: Provider cannot derive parameter information and
SetParameterInfo has not been called
______________

Any ideas?

Also, they query I'm trying to run isn't as straight forward as I originally
stated. The parameters are actually part of a string in the query
(concatenated).

Select * From WSIS_<variable here>_Course_Catalog

So if the global Variable "TERM" had a value of "SPG", the resultant SQL
would look like:

Select * from WSIS_SPG_Course_Catalog.

Thanks again.
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <e1utlcVCFHA.2568 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, news.microsoft.com
<trsharpe (AT) holly (DOT) colostate.edu> writes
That is somewhat different then. Think T-SQL, you cannot use a variable
in an object name. The restrictions apply for OLE-DB parameters. The
query engine needs to be able to create a parameterised query plan just
as it would do in T-SQL. It is not a literal replace.

In short you cannot use parameters for this, so try this-

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

Darren




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

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

That worked out great, thanks for the help.

I had a quick (and final) question about the script on the page you linked
to.
__________________________________________________ ____________
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
__________________________________________________ ____________
The name "DTSTask_DTSDataPumpTask_1"; in the design view, is there an easy
way to view this property?
I ended up having to change the _1 (by trial and error) to the appropriate
number that corresponded with the task I was editing. (it ended up being
_4).

Thanks again for the help!



"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

You can use Disconnected Edit to explore the package in detail. The
description is what you see in the designer, this should help you match what
you see in Disco Edit with the designer.

A top tip is to right-click the task, select Workflow Properties. On the
Options tab, the step name is show along with the description. It is not
guaranteed, but normally the task name and the step name are almost the
same, save for the word step or task at the beginning. Faster than using
Disoc edit.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"news.microsoft.com" <trsharpe (AT) holly (DOT) colostate.edu> wrote

Darren,

Hey, I really appreciate the help. This is the first time I've dug into DTS
at all and it's been a piece of cake with your help (and
http://ww.sqldts.com).

Travis

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote