Re: DTS Global Variables + SQL query
From: news.microsoft.com (trsharpe_at_holly.colostate.edu)
Date: 02/02/05
- Next message: softengine: "Re: DTS packages in SQL 2000 slow to open"
- Previous message: Allan Mitchell: "Re: Problem With Scheduled DTS Package"
- In reply to: Darren Green: "Re: DTS Global Variables + SQL query"
- Next in thread: Darren Green: "Re: DTS Global Variables + SQL query"
- Reply: Darren Green: "Re: DTS Global Variables + SQL query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 2 Feb 2005 13:34:28 -0700
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@reply-to-newsgroup-sqldts.com> wrote in message
news:gZXbQMV2VSACFw3y@sqldts.com...
> In message <e1utlcVCFHA.2568@TK2MSFTNGP10.phx.gbl>, news.microsoft.com
> <trsharpe@holly.colostate.edu> writes
>>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.
>>
>
> 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
>
>
>
>
>>Thanks again.
>>"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in
>>message
>>news:jdibBxKjpBACFwGf@sqldts.com...
>>> In message <#kBENgJCFHA.936@TK2MSFTNGP12.phx.gbl>, news.microsoft.com
>>> <trsharpe@holly.colostate.edu> writes
>>>>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.
>>>>
>>>>
>>>
>>> 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
>>>
>>
>>
>
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
>
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>
- Next message: softengine: "Re: DTS packages in SQL 2000 slow to open"
- Previous message: Allan Mitchell: "Re: Problem With Scheduled DTS Package"
- In reply to: Darren Green: "Re: DTS Global Variables + SQL query"
- Next in thread: Darren Green: "Re: DTS Global Variables + SQL query"
- Reply: Darren Green: "Re: DTS Global Variables + SQL query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
