Joining two server in SSIS Data Flow
Some part of my data was moved to another server. For this reason I have to change query in my OLE DB Source in SSIS. I use this moved data to return 3 columns and filter my whole data. I also have to use dynamic query, because for each day, I have few version of data and I need to take the newest. For this I use Foreach Loop which iterate through data and assign the newest version to filter. Because of this I think that Lookup transformation won't work for me.
The query looks like:
SELECT x, y, ... a.moved_data a.moved_data2 a.moved_data3 FROM old_server ... LEFT JOIN new_server a ... WHERE a.filter = ?
I can't use Merge Join because this query is to big to be sorted.
Because joined table have milions of rows I need also to use seek predicate on join and where.
All of my source are SQL Server 2008R. I also have linked this server in MS.
How to join table from different server in this case?
Why not use a Lookup transformation? Select stuff from old server. Do a Lookup to the new server table (filtering if necessary - on the NEW Server table), with Redirect No Matches to NoMatchOutput.