Bulk Copy from SQL Server to Oracle

I have a requirement for a project to move data from SQL Server to Oracle in bulk mode. There is OracleBulkCopy from DataDirect and ODP .net开发者_运维知识库 but to use that I have to first convert the data reader from SQL server into a CSV file and then can export that using bulk copy.This is a very inefficient process and I was wondering if there is another way.We can't use Linked servers.

IF you use SSIS you should be able to directly load data to an ORacle database withouthgoing through the step of creating a text file for the load. This assumes the Oracle database is availble in your network.

You can export your sql server data to a flat file or a comma separated file. You can use that flat file as an external table in Oracle.

An other solution is to create a database link from Oracle to sql server and to do 'select .. from ssss@db_link'.

What is your data volume ?

If its not too huge, then try using Access as a bridge between the two DBs.

In access, map the source SQLSERVER table, and the destination ORACLE table.

Read from source, and write into destination, using Access.

You can configure HS and establish a connection from your ORA db to the sql server. Create a table or MV which will give you what you need.

Using SqlBulkCopy and some c# code works really well.

http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server