I need to issue a query against DBs on two servers:

[CRM].[CRM].[dbo].[BasicInfo] (NationalID PK, CustomerCardNo)
[ERP].[ERP].[dbo].[Order] (OrderID PK, CustomerCardNo)

And what I want to do is like this:

select * 
from   [ERP].[ERP].[dbo].[Order] as O 
where  O.CustomerCardNo in (select CustomerCardNo 
                            from   [CRM].[CRM].[dbo].[BasicInfo]);

I think there are three ways to achieve this:

  1. linked server: my manager would probably not authorize this because they are not familiar with linked servers;
  2. open data source: company policies do not allow developers to have access to DB passwords;
  3. SSIS: seems to be the only choice left, and my company adopts SSIS widely.

When I googled I had no luck finding tutorials regarding how to use SSIS to do this. The closest question is this. Please provide me with some guidance.

  • Similar question here.
    – Ryan
    Jun 5 '17 at 2:10

There are many ways you can achieve this using SSIS:

  1. Using Lookup Transformation
  2. Using Script Task
  3. Using Execute Sql Task

Also you may find other methods...

I have a detailed answer on a similar question on stackoverflow:

  • Script tasks and execute sql tasks have limitations, since the list for IN statement cannot be too long. In my case the list would contain tens of thousands of values, where those two ways won't work.
    – Ryan
    Jun 5 '17 at 2:09

Since what you're doing is the equivalent of an inner join, give this a try: SSIS Merge Join

  • Thanks! Your method also works, but I have to accept only one answer...
    – Ryan
    Jun 5 '17 at 2:59

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.