SSIS(SQL 2008) ...taking data from Oracle

  • Dugi

    SSCoach

    Points: 17998

    Is there any possibility to design the SSIS in SQL Server 2008 to transfer data from Oracle to SQL Server!?

    Does anyone have this experience!? I want to know and some little steps how to do it!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Gift Peddie

    SSC Guru

    Points: 73570

    Dugi (1/15/2010)


    Is there any possibility to design the SSIS in SQL Server 2008 to transfer data from Oracle to SQL Server!?

    Does anyone have this experience!? I want to know and some little steps how to do it!

    You need to download Oracle connector and download and install Oracle client from Oracle site because Oracle client is required for all Oracle development. You need to add the Oracle TNSNAMES.ORA file in the folder with the client called Oracle home. If your Oracle is 8i you must use Oracle 9i RC2 client all other use 9i for 9i, 10g for 10g and 11g for 11g. While you are at Oracle also get Oracle developer. Check the thread below for some know issues.

    http://www.sqlservercentral.com/Forums/Topic822613-1044-1.aspx

    Kind regards,
    Gift Peddie

  • Dugi

    SSCoach

    Points: 17998

    Yes the Oracle version is 8i, I just ask for any possibility so thnx for the info, I just download the SSISOraAdapter from this location, so I just was interested any instruction step by step...anyway thnx for the kind reply with nice info!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Gift Peddie

    SSC Guru

    Points: 73570

    Dugi (1/15/2010)


    Yes the Oracle version is 8i, I just ask for any possibility so thnx for the info, I just download the SSISOraAdapter from this location, so I just was interested any instruction step by step...anyway thnx for the kind reply with nice info!

    You need more than the adapter because Oracle client is required for all development with Oracle and I have looked and Oracle have removed the 9i RC2 client so you need to download and install the 10g client and then you need to put the Oracle TNSNAMES.ORA file, it contains among other things your relevant permissions to Oracle in the 10g client install folder and Oracle can install in D drive. Then you go into BIDs and configure Oracle connection. Known issues 10g is known to block 9i and older Oracle connection so you need to be aware of that. If your Oracle is x64 since you are in 8i which is very old you need to make sure you use x86 which is 32bits. The first thing you do is download and install 10g client and take care of all permissions issues.

    Kind regards,
    Gift Peddie

  • Dugi

    SSCoach

    Points: 17998

    Hi Gift,

    Let me go to simplify the situation, I have Ora 8i 32 bit, and can I test these things in Virtual Machine (VMWare), so I gonna install Ora 10g or Ora 11g then make export/import data from 8i to 10g then configuring SSIS with Ora 10g!

    Can I use this scenario!?

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Gift Peddie

    SSC Guru

    Points: 73570

    Dugi (1/15/2010)


    Hi Gift,

    Let me go to simplify the situation, I have Ora 8i 32 bit, and can I test these things in Virtual Machine (VMWare), so I gonna install Ora 10g or Ora 11g then make export/import data from 8i to 10g then configuring SSIS with Ora 10g!

    Can I use this scenario!?

    I think you need to use 10g because the differences between 11g and 8i are just too many so download and install 10g client and put your TNSNAMES.ORA file in Oracle home folder before you test the connection. You should know that connecting through SQL Plus is not relevant to development in Microsoft. Here is the link of Oracle developer you can use that to connect to Oracle and look at table definition and code. It is like Enterprise manager.

    http://www.oracle.com/technology/products/database/sql_developer/index.html

    Kind regards,
    Gift Peddie

  • Dugi

    SSCoach

    Points: 17998

    Thank you for the info and link!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 7 posts - 1 through 7 (of 7 total)

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