Retrieve GlobalVariables in an ExecuteSQLTask

Question

Retrieve GlobalVariables in an ExecuteSQLTask

Rafferty Uy

SSC-Addicted

Points: 428
More actions
September 27, 2004 at 9:54 pm

#59522

Hello there,
How do you retrieve values from your global variables inside an ExecuteSQLTask?
something like:
SELECT * from DTSGlobalVariables("database_name").dbo.Table1
(this doesn't work)
Thanks,
Rafferty

Viewing 8 posts - 1 through 7 (of 7 total)

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

cutespn SSCommitted Points: 1669 More actions · Answer 1

Hey,

i faced the same situation some time ago, but i converted the execute sql task to active x script and then used the global variables.

sudheer

Regards,
Sudheer

Jeff Burton SSCommitted Points: 1551 More actions · Answer 2

You can enter the input/output global variables for your SQL statement (e.g, SELECT ? as gvTest) in disconnected edit to get around obvious bugs in the SQL parser in the SQL task.

Just use a question mark where you want the g.v. to be used. It will execute ok, but will not parse. For example, to use a SQL task to copy one g.v. to another, precreate them (gvTest string initialized to 1 and gvTestOut string unitialized). Then change enter gvTest for parameter InputGlobalVariableNames and enter gvTestOut for parameter OutputGlobalVarialbleNames. Execute the SQL task and you'll see that it executes OK and will copy from the one g.v. to the other.

Jeff

sontracteur SSC Veteran Points: 290 More actions · Answer 3

Hi,

I don't think your problem has something to with "retreiving a global variable value".

What I understand is that in your query the table name is a variable. The only way to do it is to dynamicaly construct the query and then execute it ! So using ActiveX is not a by idea, but has poor performances in many cases ...

So, what I would try is making an ExecuteSQLTask wich calls a stored procedure wich will build and execute my dynamic query and to witch I will pass my global variables values.

I hope this could help.

regards.

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 4

the problem with using parameters is that it does not allow parameters to be used as database names... so it does not allow a statement like

SELECT * FROM ?.dbo.Table1

but anyway, I was able to solve the problem. I just made a "work-around" for this. What I did was to insert the database name in a GlobalVariables table inside my database

INSERT INTO GlobalVariables(databaseName) values(?);

and assigned ? to get the database name in the DTS global variable. then I modified the sql statements i had using this

DECLARE @dbName VARCHAR(20)

SELECT @dbName = databaseName FROM GlobalVariables

DECLARE @command VARCHAR(1000)

SET @command = 'SELECT * from ' + @dbName + '.dbo.Table1'

exec(@command)

Thanks for all the help!

Rafferty

Robert Grutko Grasshopper Points: 22 More actions · Answer 5

Suppose you want to use the SQL query with the global variable as the table name, in a transformation?

For example, I have an Oracle DB where many of the tables are the same structure, so I want to pull data out of one or more of them based on some other input in my SqlServer DB.

I created an ActiveX script that creates a Global Variable of the SqlStatement I want to execute, but I can't use it in a Transform Data Task as the transofrmations are not set.

I think that an execute SQL command won't work, since I am moving data between databases.

Rafferty Uy SSC-Addicted Points: 428 More actions · Answer 6

I'm not sure if this will work for you, but from my code above...

DECLARE @dbName VARCHAR(20)

SELECT @dbName = databaseName FROM GlobalVariables

DECLARE @command VARCHAR(1000)

SET @command = 'SELECT * from ' + @dbName + '.dbo.Table1'

exec(@command)

I think you just need to change the SET command to something like:

SET @command = 'SELECT * from YourDatabase.dbo.' + @tableName

I'm not very sure about this though, I'm not sure what you mean by the execute SQL command being not able to work.

Brian Cidern SSC Veteran Points: 263 More actions · Answer 7

I love using this method for things like this.

Open the Disconnected Edit dialog. Locate the Execute SQL task and get the exact name. It's ususally something like "DTSTask_DTSExecuteSQLTask_1" (copy to clipboard for now)

Create an ActiveX Script Task in your DTS with the following:

Function Main()
    ' query the GV's
    Dim gv_dbName, gv_tableName
    gv_dbName = DTSGlobalVariables("gv_dbName")
    gv_tableName = DTSGlobalVariables("gv_tableName")

    ' build the dynamic SQL
    Dim sz_sql
    sz_sql = "SELECT * FROM " & gv_dbName & ".dbo." & gv_tableName

    ' dynamically set the SQL statement in your Task object.  😉
    Dim o_task
    Set o_task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_1")
    o_task.Properties("SQLStatement") = sz_sql
    Set o_task = Nothing
    Main = DTSTaskExecResult_Success
End Function