1

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:

  1. Fetch the inner query and save it in a variable (var1).
  2. 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.

1

you need two datasources and you have to use the lookup transformation to filter the Server1.

enter image description here

0

A couple of other approaches too.

You could fetch the IDs as you propose, but fetch them as JSON. eg

select concat('[', string_agg(UniqueID,','),']') from table2;

and then pass the resulting JSON string as a parameter:

SELECT Name, Email
FROM table1
WHERE UniqueID IN ( SELECT [Value] FROM openjson(@ids) )

Or load the IDs into a temp table on Server1, and reference them in your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.