Accessing global DTS package variables from SQL code in a DTS task 
Author Message
 Accessing global DTS package variables from SQL code in a DTS task
Hello.

I'd like to know whether it is possible to access the DTS package
global variables from SQL code within this package.
Example : say I define a global variable Path in my DTS package, and I
initialize it when I run the package (DTSGlobalVariables("Path") =
"MyPath"). Next, I'd like to get this variable's value in a SQL script
I use in DTS. I create some SQL task, write my code in it, but... How

Thanks in advance for your answers.
Patrick.



Tue, 09 Mar 2004 17:42:57 GMT
 Accessing global DTS package variables from SQL code in a DTS task

Hi,

I'm not sure whether this is a recommended or widely-used strategy, but what
you might like to do is use a table to store "global" variables. Then you
could retrieve the value using a simple t-sql select statement...;

hth
R.


Quote:
> Hello.

> I'd like to know whether it is possible to access the DTS package
> global variables from SQL code within this package.
> Example : say I define a global variable Path in my DTS package, and I
> initialize it when I run the package (DTSGlobalVariables("Path") =
> "MyPath"). Next, I'd like to get this variable's value in a SQL script
> I use in DTS. I create some SQL task, write my code in it, but... How

> Thanks in advance for your answers.
> Patrick.



Tue, 09 Mar 2004 18:06:01 GMT
 Accessing global DTS package variables from SQL code in a DTS task
You may find this bit of code useful.

create proc sp_RunPackage
(


)
as

set nocount on

declare





        'DTS.Package'



,        'LoadFromSQLServer'
,        null







,        'GlobalVariables ("Message").Value'

exec sp_OAMethod

,        'Execute'



,        'GlobalVariables ("BuriedTreasure").Value'

exec sp_OADestroy

go

--
Tom

-------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
www.apress.com


Quote:
> Hello.

> I'd like to know whether it is possible to access the DTS package
> global variables from SQL code within this package.
> Example : say I define a global variable Path in my DTS package, and I
> initialize it when I run the package (DTSGlobalVariables("Path") =
> "MyPath"). Next, I'd like to get this variable's value in a SQL script
> I use in DTS. I create some SQL task, write my code in it, but... How

> Thanks in advance for your answers.
> Patrick.



Tue, 09 Mar 2004 19:58:05 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. DTS VB custom task reading a global variable and modifying a global variable

2. DTS: Accessing global variables from Execute SQL tasks

3. Accessing DTS global variables from Send Mail Task

4. DTS: Using Global Variables in a DT SQL Query

5. Viewing global variables in a DTS package (SQL 7 with SP1)

6. DTS global variable reference in an Execute Process Task

7. DTS Packages and Global Variables

8. using global variables in a DTS package

9. passing global variables between DTS packages

10. Setting Global variable from another DTS package.

11. Using Global Variables in a DTS Package

12. Setting DTS Package Global Variables via ASP


 
Powered by phpBB® Forum Software