4 Replies Latest reply: Dec 31, 2012 2:11 AM by Saro RSS

    Custom mapping

    Saro
      Hi friends,

      Im currently practising of performing a custom load by building custom mapping in informatica.

      For that as a first step,

      <li> I created a folder Custom ETL in the Repository manager

      <li> Imported the source table and created a target table with in the folder Custom ETL in the designer

      I have used three source tables

      a_product_types
      products
      product_attribs

      Created a target table named test.

      <li> Created a mapping b/w my source and target table in designer and saved the mapping under the name Custom ETL in my Custom ETL folder itself.

      <li> As a next step created the workflow in wf manager named CustomETL in which i have included the above mapping within the worfklow.

      <li> Next as a part of registeration in DAC, i created source system folders and defined source and target tables under the task and also referred the custom mapping for the task in the DAC.

      <li> After that i included the above created task to the custom subject area.

      <li> Finally created a EP and added the custom SA, defined the parameters builded it and run in DAC.

      As a result the load is failing with the following errors like in the status description of DAC like
      2012-12-28 17:49:58.375 INFORMATICA TASK:Custom ETL:Custom ETL:(Source : FULL Target : FULL) has started.
      
      
      
      ANOMALY INFO::: Error while executing : INFORMATICA TASK:Custom ETL:Custom ETL:(Source : FULL Target : FULL)
      MESSAGE:::
      Irrecoverable Error
      Request to start workflow : 'Custom ETL' has completed with error code 4
      Error Message : Usage error. You passed the wrong parameters to pmcmd.
      Command Used: pmcmd startworkflow -sv Oracle_BI_DW_Base_Integration_Services -d Domain_biserver -u Administrator -p ****  -f Custom ETL  -paramfile C:\Informatica\9.1.0\server\infa_shared\SrcFiles\\DataWarehouse.HEIS_TARGET.Custom ETL.Custom ETL.txt Custom ETL
      Workflow Message: 
      
      =====================================
      STD OUTPUT
      =====================================
      ERROR: Unexpected argument [-paramfile].
      Usage: pmcmd startworkflow
                                 <<-service|-sv> service <-domain|-d> domain [<-timeout|-t> timeout]>
                                 <<-user|-u> username|<-uservar|-uv> userEnvVar>
                                 <<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>
                                 [<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv> userSecuritydomainEnvVar>]
                                 [<-folder|-f> folder]
                                 [<-startfrom> taskInstancePath [<-recovery|-norecovery>]]
                                 [<-paramfile> paramfile]
                                 [<-localparamfile|-lpf> localparamfile]
                                 [<-osprofile|-o> OSProfile] [-wait|-nowait]
                                 [<-runinsname|-rin> runInsName] workflow
      
      =====================================
      ERROR OUTPUT
      =====================================
      
      EXCEPTION CLASS::: com.siebel.analytics.etl.etltask.IrrecoverableException
      
      com.siebel.analytics.etl.etltask.InformaticaTask.doExecute(InformaticaTask.java:183)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:411)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:307)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:214)
      com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:586)
      com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
      java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      java.util.concurrent.FutureTask.run(FutureTask.java:138)
      java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
      java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
      java.lang.Thread.run(Thread.java:619)
      
      (Number of retries : 1)
      
      pmcmd startworkflow -sv Oracle_BI_DW_Base_Integration_Services -d Domain_biserver -u Administrator -p ****  -f Custom ETL  -paramfile C:\Informatica\9.1.0\server\infa_shared\SrcFiles\\DataWarehouse.HEIS_TARGET.Custom ETL.Custom ETL.txt Custom ETL
      
      2012-12-28 17:50:07.609 INFORMATICA TASK:Custom ETL:Custom ETL:(Source : FULL Target : FULL) has finished execution with Failed status.
      What might be the problem with this simple custom load. Is im missing any steps in that.

      Thanks

      Regards,
      Saro
        • 1. Re: Custom mapping
          Srini VEERAVALLI
          Hope you have given custom folder name at EP->Parameters; if this is having right value then check the session properties for parameter file!.
          • 2. Re: Custom mapping
            Saro
            Hi Svee,

            Yes i have custom folder name @ EP in parameters

            I checked in the parameter file under the below path
            C:\Informatica\9.1.0\server\infa_shared\SrcFiles
            These are those contents
            [Custom ETL.Custom ETL]
            $$ANALYSIS_END=01/01/2011 12:59:00
            $$ANALYSIS_END_WID=20110101
            $$ANALYSIS_START=12/31/1979 01:00:00
            $$ANALYSIS_START_WID=19791231
            $$COST_TIME_GRAIN=QUARTER
            $$CURRENT_DATE=12/27/2012
            $$CURRENT_DATE_IN_SQL_FORMAT=TO_DATE('2012-12-27', 'YYYY-MM-DD')
            $$CURRENT_DATE_TIME_IN_SQL_FORMAT=TO_DATE('2012-12-27 19:38:33', 'YYYY-MM-DD HH24:MI:SS')
            $$CURRENT_DATE_WID=20121227
            $$CURRENT_ETL_START_TS=TO_DATE('2012-12-17 23:39:30', 'YYYY-MM-DD HH24:MI:SS')
            $$DATASOURCE_NUM_ID=1000
            $$DEFAULT_LOC_RATE_TYPE=Corporate
            $$DFLT_LANG=US
            $$ETL_PROC_WID=22624447
            $$FILTER_BY_LEDGER_ID='Y'
            $$FILTER_BY_LEDGER_TYPE='Y'
            $$GBL_CALENDAR_ID=16 Period~165
            $$GBL_DATASOURCE_NUM_ID=9
            $$GLOBAL1_CURR_CODE=USD
            $$GLOBAL1_RATE_TYPE=Corporate
            $$GLOBAL2_CURR_CODE=USD
            $$GLOBAL2_RATE_TYPE=Corporate
            $$GLOBAL3_CURR_CODE=USD
            $$GLOBAL3_RATE_TYPE=Corporate
            $$HI_DATE=TO_DATE('3714-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$HI_DT=01/01/3714 12:00:00
            $$HR_ABSNC_EXTRACT_DATE=TO_DATE('1980-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$HR_WRKFC_ADJ_SERVICE_DATE='N'
            $$HR_WRKFC_EXTRACT_DATE=TO_DATE('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$HR_WRKFC_SNAPSHOT_DT=TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$HR_WRKFC_SNAPSHOT_TO_WID=20121227
            $$Hint_Tera_Post_Cast=
            $$Hint_Tera_Pre_Cast=
            $$INITIAL_EXTRACT_DATE=09/05/1995
            $$INVPROD_CAT_SET_ID=27
            $$INV_PROD_CAT_SET_ID10=
            $$INV_PROD_CAT_SET_ID1=27
            $$INV_PROD_CAT_SET_ID2=
            $$INV_PROD_CAT_SET_ID3=
            $$INV_PROD_CAT_SET_ID4=
            $$INV_PROD_CAT_SET_ID5=
            $$INV_PROD_CAT_SET_ID6=
            $$INV_PROD_CAT_SET_ID7=
            $$INV_PROD_CAT_SET_ID8=
            $$INV_PROD_CAT_SET_ID9=
            $$LANGUAGE_CODE=E
            $$LAST_EXTRACT_DATE=
            $$LAST_EXTRACT_DATE_IN_SQL_FORMAT=
            $$LAST_EXTRACT_DATE_WID=
            $$LAST_TARGET_EXTRACT_DATE_IN_SQL_FORMAT=
            $$LEDGER_ID_LIST=1
            $$LEDGER_TYPE_LIST='PRIMARY','SECONDARY','NONE'
            $$LOAD_DT=TO_DATE('2012-12-27 19:37:47', 'YYYY-MM-DD HH24:MI:SS')
            $$LOW_DATE=TO_DATE('1899-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$LOW_DT=01/01/1899 00:00:00
            $$MASTER_CODE_NOT_FOUND=
            $$ORA_HI_DATE=TO_DATE('4712-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
            $$PROD_CAT_SET_ID10=
            $$PROD_CAT_SET_ID1=2
            $$PROD_CAT_SET_ID2=
            $$PROD_CAT_SET_ID3=
            $$PROD_CAT_SET_ID4=
            $$PROD_CAT_SET_ID5=
            $$PROD_CAT_SET_ID6=
            $$PROD_CAT_SET_ID7=
            $$PROD_CAT_SET_ID8=
            $$PROD_CAT_SET_ID9=
            $$PROD_CAT_SET_ID=2
            $$SOURCE_CODE_NOT_SUPPLIED=
            $$TENANT_ID=DEFAULT
            $$WH_DATASOURCE_NUM_ID=999
            $DBConnection_OLAP=HEIS_TARGET
            $DBConnection_OLTP=GCBC_SALES
            $PMSessionLogFile=GCBC_SALES.HEIS_TARGET.Custom ETL.Custom ETL.log
            But i couldn't find any sesslogs for the task under the path
            C:\Informatica\9.1.0\server\infa_shared\sesslogs
            Regards,
            Saro
            • 3. Re: Custom mapping
              Srini VEERAVALLI
              Since mapping is not stated running you wont find the session log.

              You need update the parameterfileOLTP.txt file from DAC parameters folder.
              Just copy existing source content and rename it for new custom workflow.

              Also make sure you keep session and workflow names are same.

              Edited by: Srini VEERAVALLI on Dec 28, 2012 9:29 AM
              • 4. Re: Custom mapping
                Saro
                Dear Svee,

                Thanks a lot, soon after adding a parameter file entry and after making sure of wf name and session name(of same, without any spaces).

                I ensured a successfully DAC run. Thanks for your peaceful help.

                Regards,
                Saro