CODEWITHSUNDEEP
sql-server
dtc
dtc

Reputation: 10296

Working with data from Oracle and SQL Server

I am trying to find out what options I have if I wanted to query Oracle and SQL Server data at the same time.

Here is the scenario:

Oracle database contains dataset A SQL Server database contains dataset B, C, D

I need to take dataset A and join it against B, C and D separately in order to generate certain results and counts.

Current solution:

  1. Query Oracle, export the data into SQL Server insert statements.
  2. In SQL Server Management Studio run the SQL Server insert statements that I generated from Oracle to insert data into a temp table
  3. Join the temp table with datasets B, C and D as needed

I'm looking for solutions that will cause the least amount of calls and workload to the databases.

Edit: I forgot to mention that linked servers or anything that changes server configuration are not allowed.

Upvotes: 1

Views: 937

Answers (1)

Aaron
Aaron

Reputation: 57748

You can accomplish this by writing an MSSQL SSIS package. This would allow you to JOIN Oracle data with MSSQL data. SSIS packages are typically used for updating or moving data from one table/server to another, so the feasibility might depend on your underlying goals for this task/project.

Here's a short tutorial on creating an SSIS package. And here's a link to instructions on how to connect to an Oracle data source from within an SSIS package.

Upvotes: 1

Related Questions