Post reply

SSIS connecting to oracle on another machine

  • March 6, 2015 at 2:14 am

    #398134

    Hi,

    For last two days I’m trying to build a package to transfer data from Oracle DB to SQL Server and vice versa. I have another PC that is running Oracle databases (installed some time ago in the past by someone) for development purposes. It’s kind of hard to start building the package as I can’t connect from my PC to the oracle using SSIS. I’ve tried this:

    https://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

    and this:

    http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx

    and some other sources but I don’t seem to be getting anywhere and I have never worked with oracle before so it makes it even harder.

    Is there anything I have to set up on the PC that is running oracle to be able to connect to it through the network or am I missing something on my PC? IF I will be able to finally work everything out somehow I’ll have to recreate the setup on customer site and make the package running.

    What information do you need to be able to help me?

  • March 6, 2015 at 2:32 am

    #1782105

    I just found this, let me try it and will let you know how it goes.

    http://wiki.gieschen.it/doku.php?id=it:microsoft:ora64ssis

  • March 6, 2015 at 5:21 am

    #1782134

    I'm lost, please help...

  • March 6, 2015 at 8:18 am

    #1782185

    Kutang Pan (3/6/2015)

    I'm lost, please help...

    To verify, are you using the Attunity Oracle connectors? Have you installed both the 32 and 64-bit versions on your SSIS machine?

  • March 9, 2015 at 1:54 am

    #1782430

    Martin Schoombee (3/6/2015)

    Kutang Pan (3/6/2015)

    I'm lost, please help...

    To verify, are you using the Attunity Oracle connectors? Have you installed both the 32 and 64-bit versions on your SSIS machine?

    HI Martin,

    Yes i did install both 64 and 32 and i have oracle source and destination visable in my SSIS. However more i look into it more I'm convinced it's incorrect setup of the development oracle server preventing me from connecting not my SSIS. Does enyone have some sort of instructions how to setup oracle server for development purposes on another machine or Virtual Box?

  • March 9, 2015 at 9:09 am

    #1782571

    That may be a better question for the Oracle forums.

  • March 9, 2015 at 9:09 am

    #1782572

    Finally i've got a success connection message !!

    I had to play a little bit with Oracle Net Manager and now using ServerName:Port/ServiceName and SYSTEM login i'm able to connect.

    Now... I can see a lot of tables but i can't see the ones I'm interested in. I get a long list as on attached screen. Any suggestions why? Is it agains something to do with access on Oracle side?

  • March 9, 2015 at 9:12 am

    #1782574

    Probably has to do with the fact that it is showing all tables within all schemas. You may want to change your connection string to connect to the schema you'd like to access only.

  • March 10, 2015 at 4:52 am

    #1782836

    Hi Martin,

    I had to create that table within my logins tablespace, I have no idea why i couldn't grant access to that table instead of recreating it under different schema. Anyway it's good enough for developing a package.

    Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply