Variables in DTS package

[JoeT]

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[Jun]

You do almost everything using your SQL Execute Tasks in your DTS package. If you want to pass parameters, just create Stored Procedures and Execute your stored procedure(s) there. You can combine all your data tranaformation processes with the DTS data transferring functions.

Just remember to use @whatever for local variable (existing in one SQL script or one Task) and use @@whatever as global variable as you would in VB. Only thing you need to take care is to AVOID using the same global variable names as the system globle variables( you can find out those system global variable in SQL's BOL.

Hope this helps!

Jun


------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[Carly]

Have a look at http://www.swynk.com/friends/knight/

------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[JoeT]

My testing shows that I can't use a user-def. local variable (@whatever) or a user-defined global variable (@@whatever) across multiple "Execute SQL" tasks within the same DTS package (w/o using sp's). Can you elaborate on the 2nd paragraph of your answer?

Thanks,

Joe
------------
Jun at 12/8/00 11:56:24 AM

You do almost everything using your SQL Execute Tasks in your DTS package. If you want to pass parameters, just create Stored Procedures and Execute your stored procedure(s) there. You can combine all your data tranaformation processes with the DTS data transferring functions.

Just remember to use @whatever for local variable (existing in one SQL script or one Task) and use @@whatever as global variable as you would in VB. Only thing you need to take care is to AVOID using the same global variable names as the system globle variables( you can find out those system global variable in SQL's BOL.

Hope this helps!

Jun


------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[Jun]

Hi JoeT,

If you want to pass you variable @whatever to next Exec SQL Task, you have to use Stored Procedures. When you define a local variable (@whatever), it can ONLY exist on ONE session (you cann't expect the variable you defined in one ssession crosses to another session). But my point is that if you make all your process in ONE big SQL script, you would not need to pass your @whatever. You use that @whatever in your SET statement to assign value to columns or use it in your WHERE clause.

For @@Whatever


------------
JoeT at 12/8/00 1:04:15 PM

My testing shows that I can't use a user-def. local variable (@whatever) or a user-defined global variable (@@whatever) across multiple "Execute SQL" tasks within the same DTS package (w/o using sp's). Can you elaborate on the 2nd paragraph of your answer?

Thanks,

Joe
------------
Jun at 12/8/00 11:56:24 AM

You do almost everything using your SQL Execute Tasks in your DTS package. If you want to pass parameters, just create Stored Procedures and Execute your stored procedure(s) there. You can combine all your data tranaformation processes with the DTS data transferring functions.

Just remember to use @whatever for local variable (existing in one SQL script or one Task) and use @@whatever as global variable as you would in VB. Only thing you need to take care is to AVOID using the same global variable names as the system globle variables( you can find out those system global variable in SQL's BOL.

Hope this helps!

Jun


------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[Jun]

I am sorry that I press the wrong button when copy and paste info before I finish the message. Here is what I have not finished:

/************************************************** ********/
Global Variables
Global variables are used in situations where you need to pass data or object references between several different Microsoft® ActiveX® scripts in a single package. You can create global variables in several different ways.

Using the DTS Package Properties Dialog Box
The Variables table in the Global Variables tab of the DTS Package Properties Dialog Box allows you to enter global variable names and initial values.

For example, you can define a global variable named counter with an initial value of 0, and, in an ActiveX script, use it to test conditions such as the number of retries of a connection. After a connection is made, you use an ActiveX script to reset the value of counter to 0.

Any time a step containing a global variable is executed, the value for the global variable is updated in the DTS Package Properties dialog box.

Setting and Getting a Global Variable
The following two lines of ActiveX script code, written in Microsoft Visual Basic® Scripting Edition, get and set a global variable:

globalVal = DTSGlobalVariables("global&#34.Value
DTSGlobalVariables("myglobalVar&#34.Value = 200


The following ActiveX transformation script, written in Microsoft Visual Basic Scripting Edition, accesses a global variable:

function main
DTSDestination("DestColumn&#34 = DTSSource("SourceColumn&#34 &
DTSGlobalVariable("myglobalVar&#34.Value
end function



Creating a Global Variable Dynamically from Within an ActiveX Script
You can dynamically create a global COM object from within an ActiveX script in the DTS Designer. In the following example, Visual Basic Scripting Edition code is used to create an ADO connection, which can be used by several different scripts in the package to execute SQL commands and examine ADO recordsets:

DTSGlobalVariables("adoConnection&#34.Value =
CreateObject("adoConnection&#34


COM objects, such as the ADO connection shown here, can be referenced by ActiveX scripts:

function main
dim cnn1
set cnn1 = DTSGlobalVariables("adoConnection&#34.value
cnn1.provider = "sqloledb"
cnn1.open "(local)", "sa", ""
. . .
end function



This example demonstrates how to create and manipulate a global ADO connection object using Microsoft JScript®:

function Main()
{
DTSGlobalVariables("adoConn&#34.Value = CreateObject("ADODB.Connection"

conn = DTSGlobalVariables("adoConn&#34.value
conn.open("provider = sqloledb;data source = (local);user id = sa"
conn.DefaultDatabase = "tempdb";

conn.execute( "Create Table MyGlobalVariable (c1 int) " );

return(DTSTaskExecResult_Success);
}


See Also
DTS Package Properties

/************************************************** ****************/





------------
JoeT at 12/8/00 1:04:15 PM

My testing shows that I can't use a user-def. local variable (@whatever) or a user-defined global variable (@@whatever) across multiple "Execute SQL" tasks within the same DTS package (w/o using sp's). Can you elaborate on the 2nd paragraph of your answer?

Thanks,

Joe
------------
Jun at 12/8/00 11:56:24 AM

You do almost everything using your SQL Execute Tasks in your DTS package. If you want to pass parameters, just create Stored Procedures and Execute your stored procedure(s) there. You can combine all your data tranaformation processes with the DTS data transferring functions.

Just remember to use @whatever for local variable (existing in one SQL script or one Task) and use @@whatever as global variable as you would in VB. Only thing you need to take care is to AVOID using the same global variable names as the system globle variables( you can find out those system global variable in SQL's BOL.

Hope this helps!

Jun


------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.

[JoeT]

A "quick-fix" approach that works is to create a ##table with one column, stuff the desired value into that column and then run the DTS package. All "Execute SQL" tasks in the package can refer to and use this value.

Thanks Jun (anyways) for your feedback.

Joe


------------
Jun at 12/8/00 2:41:42 PM

Hi JoeT,

If you want to pass you variable @whatever to next Exec SQL Task, you have to use Stored Procedures. When you define a local variable (@whatever), it can ONLY exist on ONE session (you cann't expect the variable you defined in one ssession crosses to another session). But my point is that if you make all your process in ONE big SQL script, you would not need to pass your @whatever. You use that @whatever in your SET statement to assign value to columns or use it in your WHERE clause.

For @@Whatever


------------
JoeT at 12/8/00 1:04:15 PM

My testing shows that I can't use a user-def. local variable (@whatever) or a user-defined global variable (@@whatever) across multiple "Execute SQL" tasks within the same DTS package (w/o using sp's). Can you elaborate on the 2nd paragraph of your answer?

Thanks,

Joe
------------
Jun at 12/8/00 11:56:24 AM

You do almost everything using your SQL Execute Tasks in your DTS package. If you want to pass parameters, just create Stored Procedures and Execute your stored procedure(s) there. You can combine all your data tranaformation processes with the DTS data transferring functions.

Just remember to use @whatever for local variable (existing in one SQL script or one Task) and use @@whatever as global variable as you would in VB. Only thing you need to take care is to AVOID using the same global variable names as the system globle variables( you can find out those system global variable in SQL's BOL.

Hope this helps!

Jun


------------
JoeT at 12/8/00 11:01:46 AM

I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.

What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.

Does anyone know if this functionality exists (directly or indirectly)?

Thanks,

Joe T.