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?

Answers


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.


Need Your Help

Select a = 1 in MongoDb

mongodb mongodb-query

how can translate this SQL query in MongoDb Query ? Is it possible ?

Configurable location of keystore in WSS4J

java wss4j

I have a spring context that initializes CXF web service and wraps it with signature checking: