Design Strategy: Query and Update data across 2 different databases

Question

We have a requirement in which we need to query data across 2 different databases ( 1 in SQL Server and other in Oracle).

Here are the scenarios which need to be implemented:

1. Query: Get the data from one database and match for values in other
2. Update: Get the data from one database and update the objects in other

Technology that we are using: ASP.net, C#

The options that we have thought about:

1. Staging area in one database
2. Link Server ( can't go with the approach as it is not allowed due to organization wide policy)
3. Create web services
4. Create 2 different DAL and perform list operations with the data from 2 sources in DAL

I would like to know what is the best design strategy to deal with this kind of a scenario? If yes, then what are the pros and cons of that approach

Answer 1

Is it not possible to use SSIS package to do the data transformation between 2 servers and invoke it either via ASP.Net & c# project or via schedule job invoked on demand?