I need to do a cross-server query in SSIS and have been having a hard time figuring out how to do it. Here is what I am trying to achieve:
SELECT Name, Email FROM Server1.dbo.table1 WHERE UniqueID IN (SELECT UniqueID FROM Server2.dbo.table2)
The inner query (which is on a separate database on a separate server) will return a small list of identifiers - no more than a few dozen. Instead of explicitly doing a cross-server query, I would like to do the following:
- Fetch the inner query and save it in a variable (var1).
- Execute a second query that like this:
SELECT Name, Email FROM Server1.dbo.table1 WHERE UniqueID IN var1
I think this should be pretty simple to do, but I don't know how to implement it. I am comfortable setting up database connections and doing some configurations in the db source object. The parts I am not sure about is how to save the result set from the first query to a variable, and then how to use that variable in the second query. For example I think I need to pass it as a parameter, but I don't know if I have to format the result set into a list of comma separated values, or if I can just pass the object directly to the second query.
If someone could give me some pointers, or maybe even a couple screenshots to demonstrate how to set this up I would be very appreciative.