SQL Server Performance Forum – Threads Archive

Exporting Data to Oracle

Hi: I would want to discuss in detail different options that are available in Importing & Exporting data between Oracle and SQL Server. Here is my requirement. I have to export data in mulitple flat files and a table from Oracle to different tables in Oracle Server. Flat files and Oracle server are on different networks. I see the following as the available options 1. I have SQL Server on the source network. So, I am thinking of using DTS to import all the flat files into SQL Server tables and from here exporting the data back to Oracle Tables. 2. Transfer all the flat files to destination network using FTP. Write some scripts (PHP/ Java) which runs on the Oracle server to load the data from flat files to Oracle Server. Please discuss on 1. Advantages and disadvantages of both the options
2. Which option will be more reliable
3. Which option is considered to be more secure.
4. Problems if any with option 1( like changes in configuration on oracle server etc. ) Thanks

Pros
——–
1) Possibly more reliable, or, at least you get all the builtin error handling and reporting inherent to DTS.
2) Option 2 is self sufficient, in that it doesnt rely on SQL Server. Cons
——–
1) You would be using SQL Server to do something it is otherwise not involved in. While this isnt a problem, can you be sure SQL Server will always be on the same network, and will always have access to the two resources? Remember its one more thing to potentially go wrong when you install windows updates, sql server patches etc. 2) Lots of work!
2) Difficult to write robust error handling around the FTP protocol. How will the importer know when to begin importing? How will the importer know that the FTP hasnt aborted, and the files are incomlpete ?
Given the choice I would probably use 1. But are there more choices?
Doesnt Oracle provide an import/export tool, that would be the best option? It seems very odd if it doesnt. Security
——–
I guess it depends what you mean by security. Technically both DTS and FTP and transferring the data across the network in unencrypted format. Any varchars etc would be readable to anyone snooping the network. DTS could be more secure if SQL was setup to use an encrypted network library, but this is somethign Oracle would also need to support. Obviously this would be also sensitive to changes in oracle config, SQL Server would always need access to the text files, and be able to connect to Oracle. But using DTS, it would fail if this wasnt the case, and you would set up DTS to email you in such circumstances

[FONT=å®‹ä½“]Hi,[/FONT][FONT=å®‹ä½“][/FONT]
[FONT=å®‹ä½“]Try to use the tool at [/FONT][FONT=å®‹ä½“]http://www.raqsoft.com/[/FONT][FONT=å®‹ä½“][/FONT]
[FONT=å®‹ä½“]You can get it for free,I think it can help you solve your problem.[/FONT]

]]>