SSIS - Using a server variable within execute sql task code

Question

Post reply

SSIS - Using a server variable within execute sql task code

Linda Hale-470385

SSC Enthusiast

Points: 122
More actions
November 22, 2010 at 7:38 am

#228911

Hi guys,
I have created a user defined variable at package level called DestinationServer, the value of which I change depending on which server I am using. I have used this in an expression for a connection manager and it works as expected.
What I am now trying to do is reference this variable in an execute sql task i.e
Insert into Table1(column1, column2)
Select (blah1, blah2)
from [user::DestinationServer].databasename.dbo.tablename
where .......
I have tried creating a parameterMapping and referenicng that:-
Insert into blahblablah(column1, column2)
Select (blah1, blah2)
from ?.databasename.dbo.tablename
but I cant seem to get it to work? Is this achievable within SSIS?
TIA
Newbie

Viewing 11 posts - 1 through 10 (of 10 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You cannot use the variable like that directly. (e.g. placing a ? instead of a servername is not an option).

You need to use dynamic SQL. Create a string that contains your SQL statement with a placeholder for the servername.

Create a variable and map this variable to your parameter (using the question mark).

Then, use the REPLACE function on the string to replace the placeholder with the variable value. You'll have your final SQL statement stored in that string variable.

Execute this SQL statement with EXEC or with sp_executesql.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

The other option is using the expressions in the OLEDB source object, and setting servername via the variable, then using that as the source for a dataflow pair.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Craig Farrell (11/22/2010)
The other option is using the expressions in the OLEDB source object, and setting servername via the variable, then using that as the source for a dataflow pair.

Unfortunately the question was about an Execute SQL Task 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

da-zero (11/22/2010)
Craig Farrell (11/22/2010)
The other option is using the expressions in the OLEDB source object, and setting servername via the variable, then using that as the source for a dataflow pair.
Unfortunately the question was about an Execute SQL Task 😉

Agreed, but since that task was:

Insert into blahblablah(column1, column2)

Select (blah1, blah2)

from ?.databasename.dbo.tablename

There are now some other approaches. 🙂

The task she's trying to do can also be done from the data flow objects, and would allow her to use it the way she's trying to, just with a slightly different approach.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

You do make some good valid points, but if the real requirement is to make it all dynamic, then there is a chance that the data flow is not an option. I guess it is up to the original poster to decide 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 6

My vote is with Da-Zero. Dynamic SQL in Execute SQL tasks. I think these run more efficiently than Data Flow tasks, especially if you will be working with big record sets.

grahamc SSCertifiable Points: 6762 More actions · Answer 7

churlbut (11/23/2010)
My vote is with Da-Zero. Dynamic SQL in Execute SQL tasks. I think these run more efficiently than Data Flow tasks, especially if you will be working with big record sets.

Why is Dynamic SQL in Execute SQL tasks more efficient with big record sets??

Im with Craig...

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 8

The Execute SQL task is better in my experience than Data Flow tasks, dynamic sql is just a method for achieving what he wants to do in the Execute SQL task. Again, this is just my observation.

Linda Hale-470385 SSC Enthusiast Points: 122 More actions · Answer 9

Thanks for all your replies, I'm going to give Da Zeros suggestion a try, will let you know the results.

Linda Hale-470385 SSC Enthusiast Points: 122 More actions · Answer 10

That worked a treat! It will save me loads of time in the future.

Thanks again!

😀