SQL Server 2005 - How do I import data from an Oracle Data Pump file?

[rmsterling1972]

All,

Subject: SQL Server 2005 - How do I import data from an Oracle Data Pump file?

Tried looking in the SQL help for this and it's not very forthcoming on how
you do this.

How do I achieve this? Can someone send me an example SSIS package OR tell
me the steps OR point me towards a resource that could help me?

Look forward to your replies.

Many thanks,
Regards,
Richard.

 

[mrdenny]

What data format is the Oracle file in? Is it in a priporaty oracle data format, or is it a text file?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rmsterling]

It is a flat file but is in Oracle format (binary).

 

[mrdenny]

You will probably want to re-export the data from the Oracle database into a csv file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rmsterling1972]

I cannot do this as the original source data contains XML data. Therefore there was no choice but to output a datapump file. I guess by your answer there is no way of importing this data?

 

[mrdenny]

Not from an Oracle output file. Microsoft's software doesn't know how to read the file.

Does oracle's export software have problems writting XML to a text file?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[rmsterling1972]

According to the Oracle DBA I have been speaking too ... yes it cannot write XML to the text file in such a way to allow easy importing the other end.

 

[MDXer]

easy importing into Oracle? With the power offered by SSIS you can read many different formats that Oracle may not be able to. If one doesn't exist for the exact XML format I'm willing to bet that with a bit of effort you could write a Script task as a source and have it read in and send the output into the data flow.

Sounds like it would be more work for the oracle guy and he just doesn't want to make the effort.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck