Execute SQL task with different servers/database

Question

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.

Answer 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.