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*/”

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

Loading multiple Files into a single table using ODI

Scenario: Consider there are multiple files in the source directory with File naming convention as “test_YYYYMMDD_hhmiss”  and we need to transfer all those files to target using a single interface. We need to follow below steps to achieve that.

Step 1: Get the files from the source server using OdiSFTP command(No need to do this step if the file is in local server)

Step2: Use ODIOSCommand with below parameters:

Command to execute: tail –q -n r /directory/test_YYYYMMDD_hhmiss(here “r” indicates the number of “r-1” number of header lines that to be removed. Suppose there are 3 header lines, then r = 4

directory indicates the source directory). This command will get the concatenated content of all the files in source directory and puts into the Output file.

Output File: /directory/tempfile.txt (This file will contain contents of all the files)

Working directory: /directory/(Source directory name which contains the files)

Step 3:Mention tempfile.txt as the resource name in the File Data store

Step 4:Run the interface using File to SQL knowledge module. This will load the contents of the files into target table.

Assumption: All the files in the source directory has the same file format.

Reducing the size of BPEL Jar File

Some times you might see the size of the BPEL jar file in MB’s like 5 to 10 MB. To eliminate this problem remove “toplink” and “Oracle XML Parser V2” from your library path as explained below

1. Right Click on your BPEL project and click on “Project Properties”

2. Click on Libraries and uncheck the check boxes for “toplink” and “Oracle XML Parser V2” as shown below

This will reduce the size of the BPEL jar significantly