This discussion is archived
7 Replies Latest reply: Jan 23, 2013 7:15 AM by 986695 RSS

Database Connection Error in Informatica Workflow Task

909384 Newbie
Currently Being Moderated
Hi,

I am new in Informatica. I have created a mapping that has source of EBS instance and target of DWH. I have created a workflow and configure task properties with source and target connection. When I start task it gives error on source connection while this connection is working fine in mapping SQ. It gives following error.

everity: ERROR
Timestamp: 1/16/2013 11:01:37 AM
Node: node01_enecora02.enec.gov.ae
Thread: READER_1_1_1
Process ID: 18126
Message Code: RR_4036
Message: Error connecting to database [
Database driver error...
Function Name : Logon
ORA-12154: TNS:could not resolve the connect identifier specified

Database driver error...
Function Name : Connect
Database Error: Failed to connect to database using user [xxapps] and connection string [source_conn].].

source_conn is ODBC of source instance.
  • 1. Re: Database Connection Error in Informatica Workflow Task
    Voltaire Journeyer
    Currently Being Moderated
    Hi,

    Two ways to fix.

    1. Create a new ODBC Connection with name "source_conn" pointing to eBS.
    2. Modify the DAC Parameters in the Execution Plan to a valid ODBC Connection. Once the dac change is done Create a Relational Connection in Informatica Workflow Manager matching DAC Connection Name set earlier.

    Let me know if this worked.

    Regards,
    Jay
  • 2. Re: Database Connection Error in Informatica Workflow Task
    909384 Newbie
    Currently Being Moderated
    Hi,

    Thanks for reply.

    1. I have created some other odbc connections pointing to ebs source but same error.

    2. I am running this task from Informatica workflow Manager not from DAC. This task extacat data from ebs source and my target is a staging table in DWH.

    regards
  • 3. Re: Database Connection Error in Informatica Workflow Task
    Voltaire Journeyer
    Currently Being Moderated
    Hi,

    In such a case In Informatica Workflow Manager the Target/Source connections needs be configured in the Workflow Manager Relational Database Connections. The way Informatica Connects with the Database is by using the ODBC connection.

    So Create ODBC Connections to match Informatica Workflow Configuration.

    Let me know if this helped.

    Regards,
    Jay
  • 4. Re: Database Connection Error in Informatica Workflow Task
    909384 Newbie
    Currently Being Moderated
    Hi,

    I have created source and target Relational Connections in workflow manager and configure task Mapping in Task Developer. This sorce odbc connection is working finr in Source Qualifier in Mapping Designer but not working in workflow task. Below is the session log.

    Severity     Timestamp     Node     Thread     Message Code     Message
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6014     Initializing session [S_AP_Trans] at [Wed Jan 16 14:44:45 2013].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6683     Repository Name: [REPOS_SERVICE]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6684     Server Name: [INT_SERVICE]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6686     Folder: [CUSTOM_AP_SDE_ORA1212_Adaptor]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6685     Workflow: [wf_AP_Trans] Run Instance Name: [] Run Id: [34901]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6101     Mapping name: XX_AP_Mapping [version CheckedOut].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6963     Pre 85 Timestamp Compatibility is Enabled
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6964     Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6827     [d04/orabi/informatica/server/infa_shared/Storage] will be used as storage directory for session [S_AP_Trans].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1805     Recovery cache will be deleted when running in normal mode.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1802     Session recovery cache initialization is complete.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6708     Using configuration property [overrideMpltVarWithMapVar,Yes]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6703     Session [S_AP_Trans] is run by 64-bit Integration Service [node01_enecora02.enec.gov.ae], version [9.0.1 HotFix2], build [1111].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24058     Running Partition Group [1].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24000     Parallel Pipeline Engine initializing.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24001     Parallel Pipeline Engine running.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24003     Initializing session run.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     CMN_1569     Server Mode: [UNICODE]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     CMN_1570     Server Code page: [UTF-8 encoding of Unicode]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6151     The session sort order is [Binary].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6185     Warning. Code page validation is disabled in this session.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6156     Using low precision processing.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6180     Deadlock retry logic will not be implemented.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6187     Session target-based commit interval is [10000].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6307     DTM error log disabled.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TE_7022     TShmWriter: Initialized
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6007     DTM initialized successfully for session [S_AP_Trans]
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24033     All DTM Connection Info: [<NONE>].
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24004     PETL_24004 Starting pre-session tasks. : (Wed Jan 16 14:44:45 2013)
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24027     PETL_24027 Pre-session task completed successfully. : (Wed Jan 16 14:44:45 2013)
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24006     Starting data movement.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6660     Total Buffer Pool size is 4809512 bytes and Block size is 532168 bytes.
    INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     DBG_21438     Reader: Source is [source_conn], user [xxapps]
    INFO     1/16/2013 2:44:46 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     BLKR_16051     Source database connection [XXSource] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
    ERROR     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     RR_4036     Error connecting to database [
    Database driver error...
    Function Name : Logon
    ORA-12154: TNS:could not resolve the connect identifier specified

    Database driver error...
    Function Name : Connect
    Database Error: Failed to connect to database using user [xxapps] and connection string [source_conn].].
    ERROR     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     BLKR_16001     Error connecting to database...
    INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24031     
    ***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
    Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_AP_TRANS] has completed. The total run time was insufficient for any meaningful statistics.

    INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24005     PETL_24005 Starting post-session tasks. : (Wed Jan 16 14:49:21 2013)
    INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24029     PETL_24029 Post-session task completed successfully. : (Wed Jan 16 14:49:21 2013)
    INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6018     The session completed with [0] row transformation errors.
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24002     Parallel Pipeline Engine finished.
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24013     Session run completed with failure.
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6022     

    SESSION LOAD SUMMARY
    ================================================

    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6252     Source Load Summary.
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1740     Table: [SQ_AP_TRANS] (Instance Name: [SQ_AP_TRANS])
         Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6253     Target Load Summary.
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1740     Table: [W_XX_AP_XACT_FS] (Instance Name: [W_XX_AP_XACT_FS])
         Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6023     
    ===================================================

    INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6020     Session [S_AP_Trans] completed at [Wed Jan 16 14:49:22 2013].
  • 5. Re: Database Connection Error in Informatica Workflow Task
    Voltaire Journeyer
    Currently Being Moderated
    Hi,

    Just to test in the Informatica Workflow Manager Manually set the Connection for Source and Target and try. You need to look out for lookup connections as well.

    Let me know if this worked.

    Regards,
    Jay
  • 6. Re: Database Connection Error in Informatica Workflow Task
    909384 Newbie
    Currently Being Moderated
    Hi,

    How can I set db connections in source and target mannualy? I am using Relational Connection.

    What exactly I have to put in Connecting String in Relational Connection Editor? ie: ODBC Name, SID, Host etc.

    I have tried all about three but same error.

    Regards

    Farooq
  • 7. Re: Database Connection Error in Informatica Workflow Task
    986695 Newbie
    Currently Being Moderated
    Connection string should be your TNS connection name like ORCL... and you should have that tns entry should present in tnsnames.ora file of informatica server machine.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points