How to Import Oracle .DMP file into SQL Server?

I have a .dmp file开发者_C百科 (oracle data) and I have to import this file into SQL Server 2008 R2. I tried google but get no clear solution. Oracle is on other machine and SQL Server is on other machine. This .DMP file has only tables and data only nothing else.

Any body has any idea?

---

You can't get there from here. The files that the Oracle export utility (classic or DataPump) generate (which, by convention, frequently use the DMP extension) are proprietary binary files. They can only be consumed by the Oracle import utility (classic or DataPump) which will only allow you to load the data into another Oracle database.

You could load the DMP file into a new Oracle database but then you'll still need to move the data from Oracle to SQL Server. It may well be easier to ignore the DMP file and pull directly from the original Oracle database. There are a variety of tools that can be used to move data from an Oracle database to a SQL Server database. If you want SQL Server to control the process, you could SQL Server Integration Services (SSIS). You could also create a linked server in SQL Server that references the Oracle database and write queries against the Oracle database via that connection. If you wanted Oracle to push the data, you could also use the Oracle Transparent Gateway with Heterogeneous Services to create a database link from Oracle to SQL Server and issue SQL against the remote SQL Server database.

There is a nice StackOverflow thread on moving data from Oracle to SQL Server. The SSIS logic is extremely similar if you're pulling from Oracle to SQL Server or pushing from SQL Server to Oracle.

---

Oracle Dumps is not readable by SQL.

Simply it cannot be, But you have different solutions

1. SQL server integration services (SSIS)
2. Link between oracle and SQL (Oracle Gateway) but it works with SQL Ent.
3. Export the data from oracle in a delimited format and insert it into SQL, but it will takes time if data is huge.

---

When I faced with the same problem, I tried to investigate the format manually (in my case the dump file was generated by Oracle EXP). I found that:

• Table definitions come as Oracle CREATE TABLE statement that can be converted into MS SQL format easily
• Most kind of data ca be extracted quite easy (text goes "as is", numeric values are stored according to IEEE 754 format)
• LOBs are stored in quite complicated way, I failed to recognize it

Then I found the tool that was able to do my migration task: https://www.convert-in.com/ord2mss.htm

Vendor said that it can migrate both exp and expdp to sql server, but I have tested it on EXP format only.