Performing an SQL task with various servers / database

Question

Can I execute an Execute SQL task in two different connection managers. For instance: I need data from ServerA / DatabaseA queries to ServerB / DatabaseB. So now I need to write a request and get data from both servers. Now 2 servers are not connected by the server, not necessary.

Is this possible, please tell me.

Answer 1

Add a data flow task with separate data flow source tasks for Server A and Server B Then attach the results using the appropriate data stream conversion task.

As an example, this data stream takes the Flat File Source and OLEDB Source tasks, sorts the results, then uses the Merge Join task for the results. It looks like your implementation will require two OLEDB Sources or (ODBC, ADO NET, etc.).

I like this method on a linked server or OPENROWSET because you do not need to configure a linked server or enable Adhoc Distributed Queries in your SQL Server data sources.

Answer 2

I had to find a way to get around the lack of linked servers before, and I did something like this. 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 

Hooray!