Montag, 23. April 2012 13:02
I have 2 different servers (One is Oracle and another is SQL Server) from which I need to retrieve the data over the internet and collect to a centralised database in SQL Server. This should be a job like process and which should run in every nights. Which is the best method to achieve this by considering SQL Server as a solution provider.
Thanks in advance,
Montag, 23. April 2012 16:55
for me it worked best by using:
- SSIS with Attunity Oracle connectors
- Oracle Client
It works smoothly. I can push 5 million data rows on a corparate network in about 30 minutes.
best regards Christian
Dienstag, 24. April 2012 06:51Moderator
Hi Maju V Poulose,
Regarding to your description, you can try to download and install the latest MyODBC 3.51 drivers. (Available at http://dev.mysql.com/downloads/connector/odbc/3.51.html)
And then you can follow the steps as below:
1. Open the ODBC Data Source Administrator
2. Start -> Programs -> Administrative Tools -> Data Sources (ODBC)
3. Click the System DNS tab and press the Add button
In the new window, choose MySQL ODBC 3.51 Driver and press the Finish button
2. Test the connection by pressing the Test button. If everything tests correctly, press the Ok button.
For more information please refer to How to: Retrieve Data from an Oracle Data Source
Regards, Amber zhang
- Als Antwort markiert Kalman TothMicrosoft Community Contributor, Editor Mittwoch, 30. Mai 2012 07:25
Dienstag, 24. April 2012 07:40
Actually I am looking for a best and secure method to transfer data over the internet between 2 different servers.
Dienstag, 24. April 2012 16:57
Please take a look at BizTalk. I have not personally used it but it provides secure communications between business servers on the internet utilizing different OSs or DB platforms. Think of it as BI on steroids.
Here is a link for you - http://www.microsoft.com/biztalk/en/us/default.aspx.
- Als Antwort vorgeschlagen Noral Kuhlmann Dienstag, 24. April 2012 16:57
Mittwoch, 25. April 2012 07:47
Hi Noral, I need to implement the transfer process only using SQL Server. So Biztalk is out of scope in my current requirement even if it is one of a good solution.
Please see the possible methods below and please tell me which one is more secured and better.
- Service Broker
- Linked Server
- BCP Command
- SSIS-direct connection with source server by exposing
- SSIS-FTP task
- .Net-File transfer
Mittwoch, 25. April 2012 20:49
SSIS direct connection could be the best in your choice list. But, create multiple SSIS packages like
Package 1. Pulling the data from oracle to Staging tables
Package 2. Pulling the data from SQL to Staging tables
Package 3. Transform the Staging table to Actual tables
So, that you improve performance, reduce the maintenance and remove dependency.
Donnerstag, 26. April 2012 07:31
Can anybody tell whether it is possible to work with CLR without exposing the remote server (if server is not in a local network). Please note that I need to fetch data from a remote server and save to my local server.
Freitag, 4. Mai 2012 19:17
hi please refer the below link for your problem.
Freitag, 11. Mai 2012 20:38Beantworter
I vote for SSIS as well. SSIS Import / Export Wizard:
- Bearbeitet Kalman TothMicrosoft Community Contributor, Editor Dienstag, 16. Oktober 2012 23:39