3 Replies Latest reply: Apr 1, 2013 5:42 AM by aetl RSS

    source file and target oracle, source is having zero records

    Sahadeva
      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
          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
            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
              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