Problem importing text file into Oracle

  • I am trying to use SSIS to import a text file into an Oracle database. However, I get the following error:

    "Test connection failed because of an error in initializing provider.

    Oracle client and networkinig components were not found."

    I do have the Oracle client and networking components installed. In addition, I have the OraOLEDB.Oracle provider available under Linked Servers in SSMS. Has anyone else come across this problem?

  • imani_technology (1/22/2010)


    I am trying to use SSIS to import a text file into an Oracle database. However, I get the following error:

    "Test connection failed because of an error in initializing provider.

    Oracle client and networkinig components were not found."

    I do have the Oracle client and networking components installed. In addition, I have the OraOLEDB.Oracle provider available under Linked Servers in SSMS. Has anyone else come across this problem?

    Why do you want to use Linked server and not connect directly to Oracle from SSIS. Connecting thru linked server will make data loading much slower.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • I don't want to use linked servers. I want to use SSIS. In fact, I am trying to move a text file to Oracle via SSIS. The problem is that I keep getting the SSIS error listed above.

  • Is your machine 64bit? The Oracle driver is available only in 32bit mode.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Yes, the machine is 64-bit! We are using 64-bit SQL Server 2005 on top of 64-bit Windows Server 2003. Is there a way around the problem?

  • imani_technology (1/28/2010)


    Yes, the machine is 64-bit! We are using 64-bit SQL Server 2005 on top of 64-bit Windows Server 2003. Is there a way around the problem?

    You have to execute your package in 32bit mode.

    Under SQL Job Agent you have to explicitly specify the path to the 32bit DTEXEC:

    C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC

    In Visual Studio (BIDS), you have to setup the debug process to use the 32bit runtime:

    1. Open the solution explorer and select the project in the tree.

    2. Right-click and select 'Properties'.

    3. When the dialog opens click on 'Debugging'.

    4. Change Run64BitRuntime property to FALSE.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • That sounds good. How do I do the actual development? I can't even make a connection to Oracle in order to make a destination object.

  • Try to reinstall your Oracle client. There must be something missing.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Thanks, we are working on that now.

  • We re-installed and got the following error:

    "Test connectionfailed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    Also, I followed the instructions regarding setting Run64BitRuntime to "False." We still get the error mentioned above.

  • imani_technology (2/1/2010)


    We re-installed and got the following error:

    "Test connectionfailed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    Also, I followed the instructions regarding setting Run64BitRuntime to "False." We still get the error mentioned above.

    Have you seen this discussion[/url]?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Does that discussion apply to Oracle 11g? I think that is the version we are using here.

Viewing 12 posts - 1 through 11 (of 11 total)

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