locked
Connecting to oracle and retrieving data to store on sql server RRS feed

  • Question

  • Hello,
    my requirements are to connect to oracle db and make a copy on the local sql server(2005) of 3 tables.
    This operation can be done only once per day.
    First, i dont know how to connect and retrieve this data from oracle inside sql server 2005.
    Second, there are about 70.000 rows in one of this tables, on the other 2 there are about 3.000 rows for each tables.
    Since the network that connect this 2 dbms is slow i cant just make a select * from the big table.

    so basically, copy every day this 3 tables from oracle to sql server.
    How can i do this?
    I suppose i have to use integration services. Am i correct?
    Do i need drivers to connect to oracle?if yes, Which are this drivers?
    How to retrieve the big table?

    thank you
    Thursday, January 7, 2010 10:15 AM

Answers

  • i have solved installing
    odac x64
    odac x32
    basic win x64
    odbc win x64
    you can find this software here
    http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
    http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html
    http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
    • Marked as answer by Todd McDermid Thursday, January 7, 2010 6:53 PM
    Thursday, January 7, 2010 6:38 PM

All replies

  •  Hi,
      
       Create a OLEDB connection by choosing a provider 'Microsoft OLEDB Provider for Oracle' and try to connect the required database by choosing the server name. If you are not able to get the connection then it might be a problem with oracle drivers. Let us know which version of Oracle you are using now.

      Please refer the following links, contains comman problems while connecting to oracle from SSIS

      http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f328b660-7814-4b06-8cca-960a3055b315

     http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c21177dc-c4ed-43cf-8912-c4428dcfb3c0/

    Lakshman
    Thursday, January 7, 2010 10:23 AM
  • Yes, you can use SSIS.
    Check this thread
    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/1745db4e-7cea-414e-8a8d-1fb6e68b9c09
    Nitesh Rai- Please mark the post as answered if it answers your question
    Thursday, January 7, 2010 10:24 AM
  • i have problems with connecting to oracle probably because my server is win 2003 x64. what a mess!!!
    basically i have tried with the oracle driver that i have used in a .net project.
    using the dtswizard.exe from the original folder i get an ora-06413
    copying the dtswizard on a folder that does not contain special chars like (), i get ora-12514

    http://img269.imageshack.us/img269/8787/070120101242.png

    here it is a discussion for this issue
    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f/

    this article http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11 seems good..
    Thursday, January 7, 2010 11:49 AM
  • i have solved installing
    odac x64
    odac x32
    basic win x64
    odbc win x64
    you can find this software here
    http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
    http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html
    http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
    • Marked as answer by Todd McDermid Thursday, January 7, 2010 6:53 PM
    Thursday, January 7, 2010 6:38 PM