Jul 14, 2010

ODI: To Check if the source file is Empty and make the Scenario run as successful

Consider the scenario in which you are creating an interface to transfer the data from file to a table. In this case the interface will fail if the file content is empty. We can skip the interface run if the file is empty and make the scenario run successful using the below steps.

Step 1: Create a variable named “CHECK_EMPTYFILE”. Go to Refresh tab and enter the below code.Be sure to select the correct Logical schema where the file exists.

“select C1
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=ONE_COLUMSNP$CRLOAD_FILE=<%=odiRef.getSchemaName("D")%>/yourfilename.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0D0ASNP$CRFILE_SEP_LINE=3BSNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=C1SNP$CRTYPE_NAME=STRINGSNP$CRLINE_OFFSET=1SNP$CRLENGTH=5000SNP$CRPRECISION=5000SNP$CRACTION_ON_ERROR=0SNP$CR$$SNPS_END_KEY*/”

image

Step 2: Use this variable in your package diagram. And on failure just end the scenario session using “ODIWait” command with “0” seconds wait. In this case your Scenario will be success even if the file content is empty in the source

image

3 comments:

  1. Hi

    Whether the file is empty or not the pkg behaves the same way meaning the execution of varible does not fail.

    Also in the description why I get this

    Exception getTargetTable("METADATA_DESC") : There is no Target Table

    ReplyDelete
  2. Hi,
    If the content of the file is empty, then variable will fail, however there is one red arrow in the picture which shows the activity in case of failure. So the package execution wont fail even if the file is empty.

    Thanks,
    Ramesh

    ReplyDelete
  3. Dear Ramesh,

    Can we pass the variable name(Which holds the file name) instead of Hard coding the file name?

    ReplyDelete