5

Can i have my Execute SQL task look at 2 different connection manager. For Instance: I need data from ServerA/DatabaseA querying against ServerB/DatabaseB. So now i need to write a query and retrieve data from both servers. Now 2 servers are not linked server, not necessarily.

Is this even possible, let me know, please.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
user1810575
  • 823
  • 3
  • 24
  • 45
  • 1
    Is it the *same* data query on both servers or are you trying to get data from one server and augment that data set with information from a different location? – billinkc Feb 13 '13 at 19:53
  • Does "not necessarily" mean that you don't want to link the servers, they aren't linked yet and you can link them, or you're unable to link the servers? You can use the command OPENROWSET if you don't want to do a linked server, see here: http://jasonhaley.com/blog/post/2004/03/23/Using-OPENROWSET-for-moving-data-from-SQL-Server-to-SQL-Server.aspx. You might also be able to do OPENDATASOURCE. – Kprof Feb 13 '13 at 20:13

2 Answers2

1

I've had to find a way to work around lack of linked servers before, and I have done something like this - Give it a try:

if object_id('tempdb..#mytemptable') is not null begin drop table #mytemptable end
select * into #mytemptable
from openrowset('SQLNCLI10','Server=TheOtherServersName;Trusted_Connection=yes;','SELECT * FROM FullyQualifiedName.dbo.MyTable')
/* Now use the temptable created on this server to do your join/subquery on whatever */
select * from MyOtherTable a
join #mytemptable b on a.id = b.id

Cheers!

Zee
  • 1,780
  • 3
  • 16
  • 27
  • 1
    Answer question instead of commenting. For SSIS, your comment is obviously a good answer. I'm not going to just copy your comment as an answer, and steal your rep though. :) – Zee Feb 13 '13 at 21:19
1

Add a Data Flow Task with separate Data Flow Source tasks for Server A and Server B. Then join results using the appropriate Data Flow Transformation task.

As an example, this data flow takes a Flat File Source and OLEDB Source task, sorts results, then uses a Merge Join task for results. It sounds like your implementation would require two OLEDB Sources or (ODBC, ADO NET, etc.).

I like this method over the linked server or OPENROWSET, because you don't have to configure a linked server or enable Adhoc Distributed Queries on your SQL Server data sources.

SSIS Data Flow

Bryan
  • 17,112
  • 7
  • 57
  • 80