Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS - Using a server variable within execute sql task code Expand / Collapse
Author
Message
Posted Monday, November 22, 2010 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 19, 2011 5:45 AM
Points: 2, Visits: 114
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





Post #1024332
Posted Monday, November 22, 2010 11:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 13,735, Visits: 10,707
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1024550
Posted Monday, November 22, 2010 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 6,259, Visits: 7,451
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1024570
Posted Monday, November 22, 2010 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 13,735, Visits: 10,707
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1024587
Posted Monday, November 22, 2010 12:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 6,259, Visits: 7,451
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1024590
Posted Monday, November 22, 2010 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 13,735, Visits: 10,707
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



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1024620
Posted Tuesday, November 23, 2010 6:13 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:53 AM
Points: 1,487, Visits: 479
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.
Post #1025095
Posted Tuesday, November 23, 2010 6:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:38 AM
Points: 563, Visits: 1,012
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...
Post #1025098
Posted Tuesday, November 23, 2010 6:26 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:53 AM
Points: 1,487, Visits: 479
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.
Post #1025106
Posted Wednesday, November 24, 2010 2:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 19, 2011 5:45 AM
Points: 2, Visits: 114
Thanks for all your replies, I'm going to give Da Zeros suggestion a try, will let you know the results.
Post #1025728
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse