This discussion is archived
3 Replies Latest reply: Apr 1, 2013 3:42 AM by aetl RSS

source file and target oracle, source is having zero records

Sahadeva Newbie
Currently Being Moderated
Hi,

I have a required in ODI and need your help to get ideas on how that could be implemented in ODI Package.

Requirement: I need to be notified if the source file is empty or having zero records. Preferably an email to say source file available, load is successful but ZERO records loaded as its empty file.

Source: Flat File(Delimited file) with 4 columns
Target: Staging table with same 4 columns.

Thx,
Sahadeva.
  • 1. Re: source file and target oracle, source is having zero records
    aetl Explorer
    Currently Being Moderated
    Hi,

    First solution you can load file to a Temp table then get count of temp table .İf count=0 then send e-mail, else load to your target table.
    Use a variable like this=Select count(*) from your_stg_table
    or use a variable like this: Select <%=odiRef.getPrevStepLog("INSERT_COUNT")%> FROM dual

    Another solution:You can use Jython code to get rowcount of your files before load the table.

    Regards
  • 2. Re: source file and target oracle, source is having zero records
    Sahadeva Newbie
    Currently Being Moderated
    Hi thx for your reply,

    Can you please let me know the Jython code to get rowcount of our files before load the table.
    Also please confirm me here do we need to follow the above steps when we use jython script in ODI, suppose my file path is C:\source assume. Here what method do I need to follow for this requirement.
    steps one by one please for better understanding.

    Thx,
    Sahadeva.
  • 3. Re: source file and target oracle, source is having zero records
    aetl Explorer
    Currently Being Moderated
    Hi,

    You can use this solution

    1-)First you can write jython code like this and load it to a table

    import java.lang as lang
    import java.sql as sql
    import java.lang.String
    import os
    import java.io.File as File
    import java.lang.Integer as Integer

    lang.Class.forName("<%=snpRef.getInfo("DEST_JAVA_DRIVER")%>")
    ConSrc=sql.DriverManager.getConnection("<%=snpRef.getInfo("DEST_JAVA_URL")%>", "<%=snpRef.getInfo("DEST_USER_NAME")%>", "<%=snpRef.getInfo("DEST_PASS")%>")
    readDBLink = ConSrc.createStatement()
    syslist = os.listdir('#SOURCE_DIRECTORY')
    syslist.sort()
         dfd=open('#SOURCE_DIRECTORY' ,'r')
         nol=0
         line=dfd.readline()
         while line !='':
              nol+=1
              line=dfd.readline()
         dfd.close()
                   sqlDBLink = "insert into HR.ETL_FILE_LOG (NUMBER_OF_ROWS) values (Integer.toString(nol) )"
                   rqteDBLink = readDBLink.execute(sqlDBLink)

    ConSrc.close()

    -You can create a table just has one column and insert into rowcount of file.Then you can insert new variable :Select count(*) from HR.ETL_FILE_LOG

    I have marked bold that get lines of files

    Edited by: aetl on 01.Nis.2013 03:40

    Edited by: aetl on 01.Nis.2013 03:40

    Edited by: aetl on 01.Nis.2013 03:42

Legend

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