Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS ideal for transfer from Oracle?

Answered SSIS ideal for transfer from Oracle?

  • Saturday, December 08, 2012 7:42 PM
     
     
    Sign In to Vote
    0

    Hi,

    I would like to get data across from Oracle 8i (8.7.1.3) to SQL Server 2008 R2 on a daily basis... can SSIS be used successfully for this transfer and are there any issues that I need to look out for?

    Thank you in advance,

     

All Replies

  • Saturday, December 08, 2012 7:50 PM
     
     
    Sign In to Vote
    0

    Hi,

    SSIS is suitable solution for taking data from oracle.

    You will need correct version of oracle provider for your version (can be downloade from oracle website).

    But consider using linked server and SQL query if the task is only about copying data from oracle, its much faster than SSIS.

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

     
  • Saturday, December 08, 2012 7:55 PM
    Moderator
     
     
    Sign In to Vote
    0

    There is a very fast Oracle connector, but I think it starts at Oracle 9.

    Also see this forum thread:
    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cf8e2a9f-f84d-4eb7-abed-4086551787a3

    And a blogpost:
    http://sql-troubles.blogspot.com/2010/06/ssis-and-oracle.html

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Thursday, December 13, 2012 11:30 AM
     
     
    Sign In to Vote
    0
    I am struggling my ass off to get the right Provider downloads to connect from SQL 2008 R2 to Oracle 8i (8.1.7.3) via linked servers - can you possibly direct me in this regard?
    Thank you so much for the reply!
     
  • Thursday, December 13, 2012 11:54 AM
     
     
    Sign In to Vote
    0

    But consider using linked server and SQL query if the task is only about copying data from oracle, its much faster than SSIS.

    Do you have any references or benchmarks that back-up your statement?

    Linked servers will have to do their utmost best if they want to outperform SSIS with the Attunity connectors.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Thursday, December 13, 2012 11:56 AM
     
     Proposed
    Sign In to Vote
    0
    I am struggling my ass off to get the right Provider downloads to connect from SQL 2008 R2 to Oracle 8i (8.1.7.3) via linked servers - can you possibly direct me in this regard?
    Thank you so much for the reply!

    You can download it here:

    Oracle10g Provider for OLE DB

    (it can connect to Oracle 8i as well)

    I believe you will also have to install the Oracle client on the machine. And set-up TNS naming and everything...

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Tuesday, December 18, 2012 10:46 AM
     
     
    Sign In to Vote
    0

    Hi Koen,

    I'm going to try that, thank you so much!

     
  • Tuesday, December 18, 2012 10:47 AM
     
     
    Sign In to Vote
    0

    Btw, what Oracle client are you referring to, and where can I find such?

    Thank you so much for the assistance!

     
  • Tuesday, December 18, 2012 11:56 AM
     
     
    Sign In to Vote
    0

    Btw, what Oracle client are you referring to, and where can I find such?

    Thank you so much for the assistance!

    The Oracle client is (unfortunately) needed on the machine in order to connect to the database.
    More information: How to setup linked servers for SQL Server and Oracle 64 bit client (the idea is the same)

    You can download it here.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Wednesday, December 19, 2012 1:06 PM
     
     
    Sign In to Vote
    0

    Hi Koen,

    I have now downloaded and installed the latest 32- and 64-bit versions of the Oracle client, and can now see the "Oracle Provider for OLA DB" provider in SSIS. However, after I entered the Oracle Sever's IP address and do a test connection, i get an error stating:

    Test connection failed because of an error in initialising provider. ORA-12541: TNS: no listener.

    I have configured a listener with the Net Configuration assistant, called LISTENER, using the default port 1521. Still no luck... what else do I need to do?

     
  • Wednesday, December 19, 2012 7:51 PM
     
     
    Sign In to Vote
    0

    I'm not really into the Oracle connection stuff, so you might be more lucky in an Oracle forum.

    Did you add the server to the TNSNAMES.ORA file?

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Thursday, December 20, 2012 2:06 PM
     
     
    Sign In to Vote
    0

    I think that's where I'm struggling at the mo... could you perhaps assist with exact step-by-steps on this?

     
  • Thursday, December 20, 2012 2:07 PM
     
     
    Sign In to Vote
    0
    Not really. I'm not an Oracle DBA and I will likely never become one.
    As I said earlier, you might have more luck in an Oracle forum.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

     
  • Friday, December 21, 2012 7:15 AM
     
     Answered
    Sign In to Vote
    0

    Hi

    SSIS Can be used to transfer data from Oracle to SQL Server 2008 R2 on a daily basis.

    But, Oracle has some limitations, in DTS Transacation, so if the daily batch failed in middle of execution you cant rollback the transaction and you might lost the data in your existing table.

    If you are truncating the data from the existing tables in SQL Server 2008 R2 and loading the data from Oracle on a daily basis, I would like to suggest as below:

    1. Create temporary tables in your SQL Server 2008 R2 same as your original tables.

    2. Copy the data from Oracle to your temporary tables without rollback DTS option.

    3. Copy the data from your temporary tables to original tables with Rollback DTS option.

    Regards

    Mohana Krishna