Using Global Variable in a query

Question

Using Global Variable in a query

jim-159060

Newbie

Points: 3
More actions
July 15, 2004 at 11:29 am

#360406

Hi,

i have a package where a load some global variables with a .ini file, and then use them in all the tasks in the package. But i don't find a way to call a global variable in a query...
i have a global variabel "DIR", and want a simples query like:
select <call global variable "DIR"> from ...
Anyone know the way?

Paulo A.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 1

You can dynamically change the SQL of a task/data pump via an ActiveX Script and pull in the global variable information like such:

myVariableName = DTSGlobalVariables("myGlobalVariableName").Value

An Example ActiveX Script:

Function Main()

Dim objPkg

Dim objTask

Dim mySQL

Dim myVar

' Get Global Variable

myVar = DTSGlobalVariables("myVar").Value

'--------------------------------------------------------------------------

' EXECUTE SQL TASK

'--------------------------------------------------------------------------

' Build SQL String

mySQL = "SELECT * "

mySQL = mySQL & "FROM myTableName "

mySQL = mySQL & "WHERE myFieldName = '" & myVar & "'"

' Set reference to the Execute SQL Task

Set objPkg = DTSGlobalVariables.Parent

Set objTask = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

' Assign SQL Statement to Source of DataPump

objTask.SQLStatement = mySQL

' Clean Up

Set objTask = Nothing

Set objPkg = Nothing

Main = DTSTaskExecResult_Success

End Function

jim-159060 Newbie Points: 3 More actions · Answer 2

Hello,

thanks for the help, i manage the problem with a script, without SQL task, bue your example is going to come handy in some future work. Thanks!

By the way, can you tell me quickly the main differences between DTS and Ascential Datastage? I' m using DTS but they have installed yesterday Datastage in my machine, which one is the best? I'm going to continue with packages to fill my Data Warehouse.

Thanks,

Paulo A.

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 3

I'm only familiar with DTS. It is part of SQL Server. As for Ascential Datastage, never used it. It sounds like just another ETL tool. Hopefully someone else in the forum can help with this issue.

jim-159060 Newbie Points: 3 More actions · Answer 4

jim-159060

Newbie

Points: 3

July 16, 2004 at 8:38 am

#514872

ok thanks anyway!

Paulo A.