How to pass global Variable to Execute SQL task?

[dobrios]

Hello there,
I need to be able to retrieve global variable value in Execute SQL task. I went through few books but didn't find anything that would point me in right direction.

Thank you for your help

Steve

 

[nigelrivett]

You can use a parameter (use a ? and set the parameter to the variable). This oesn't always work and may give a syntax error

I prefer to set the text in an activex script
see

http://www.nigelrivett.net/SetDTSRunTimeValues.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dobrios]

Thanks for the reply nigelrivett, however I can't use this solution as it works only with stored procedures, and I can't user sprocs.

What I'm trying to do is this:
I have a server where my packages are.
From this server I need to transfer data to 5 different servers and only data that doesn't exist on target server, so I need to verify data existence against target server.

In my main package I get recordset of server names and loop in Activex Script. In loop, I set global variables for child package and execute it.

In child Activex script I change one of my connections to global var (server name) from parent package. Now I need to pass global var to SQL Task as a parameter.
So I run SQL like this:
INSERT INTO myTable
(col1,
col2)
SELECT col1, col2
FROM mainServer.myDB.dbo.myTable
WHERE col1 NOT IN
(SELECT col1 FROM myTable
WHERE serverName = myGlobalVarfromParentPackage)

Because I change connection, this SQL will be valid for all 5 target servers and it will run as it's running on target server, even it's being executed from source server.

If I use sproc, I need to create separate SQL for each server to verify that only new items are being transferred. If everything would be on the same server, I'd use sproc and forget about this hassle, but ...

All I need to figure out how to pass global var to SQL task. I can do it in Activex Script but then I need to do like 20 calls as I have about 20 SQL tasks in child package.

I hope I explained clearly why I need to do it this way. Can't think of any other ways of doing it.

Thanks
Steve

 

[nigelrivett]

Have you looked at linked servers?
You can execute sql on any server from a source server via a stored proc.

The link I gave you shows how to set a sql command from a global variable - what you are trying to do is very similar.

You realise that the data will need to be transferred for the comparison so this will be slow and cause a lot of network traffic.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tb]

http://www.sqldts.com/?205

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!