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:
- linked server: my manager would probably not authorize this because they are not familiar with linked servers;
- open data source: company policies do not allow developers to have access to DB passwords;
- 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.