SSIS - OleDB source query from two database on separate servers

I'm developing an SSIS package which needs to pull data from ServerA based upon data in a DB t开发者_C百科able on ServerB. I'm DBadmin on ServerB, but very limited access to ServerA.

The query I need to execute, ideally using an OleDB source component, is like this:

SELECT 
     Blah
FROM ServerA.Database1.dbo.TableA
WHERE Something IN (SELECT foo FROM ServerB.Database2.dbo.TableB)

Is it possible to do this, or do I need to take a different approach?

EDIT: I need to run this query every ten minutes 24x7, and I don't want to pull the data from ServerA as there are millions of rows in the table, which is part of a business critical app which cannot be overloaded.

Pull from serverA into a third data source, pull from serverB into the same source, then use that source to apply your where clause.

OR, pull from serverA to serverB and apply your where clause on serverB.

In response to comment,

OR, pull from serverB to serverA and apply your where clause on serverA. That's really where you want the join done, not in your SSIS package.

Also, see if you can limit most of the rows in serverA based on some criteria outside of B or limit the amount of data from serverB that needs to exist on A for a rough cut before transferring to the SSIS package.

I'm also wondering if they could link serverB to serverA for you...