1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SSIS with Orcale 11g

Discussion in 'General' started by naresh_oracle, Mar 30, 2011.

  1. Hi All,

    I am new to SSIS . I am trying to create SSIS Package to extract data from Oracle(Which is on network) to SQl Server (Which is on my local laptop). I can connect to oracle using TOAD , when i try to import data in SSIS using oledb provider for oracle , i am getting error message : TNS No listener. Oracle is running on 1525 port.

    Following is cmd output for oracle database i am trying to connect

    Microsoft Windows [Version 6.1.7600]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\Users\>TNSPING xxx

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-MAR-2
    011 14:29:58

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    \product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

    Used EZCONNECT adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
    COL=TCP)(HOST=10.1.xxx.xxx)(PORT=1521)))
    TNS-12541: TNS:no listener

    Following is TNS NAMES

    # tnsnames.ora Network Configuration File:
    # Generated by Oracle configuration tools.

    LISTENER_TEST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    TEST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = Test.xxxxx.com)
    )
    )

    )

    xxxxxx=
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx)(PORT = 1525))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = xxxxxx)
    )
    )

    Following is Listener.ORA

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\xxxxx\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\app\xxxxxx\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = C:\app\xxxxxxx



    Following is SQLNET.ORA


    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


    Any help is greatly appreciated.

    Thanks,
    Naresh.