Code: Select all
1/26/17 10:32:12 AM ERROR [8044 - SQL] Last known record near boundaries (start line: 1, start column: 1, end line: 2, end column: 1) of file '/mars/sftp/CN/NYCBOE/inbasket/01252017NSLP_RA_DATA.TXT'
1/26/17 10:32:12 AM ERROR [8098 - SQL] ORA-00919: invalid function. Full stack trace written to '1000000028017_error_1.log'
Code: Select all
1/26/17 10:37:22 AM ERROR [8044 - SQL] Last known record near boundaries (start line: 1, start column: 1, end line: 2, end column: 1) of file '/mars/sftp/CN/NYCBOE/inbasket/01252017NSLP_RA_DATA.TXT'
Code: Select all
<project name="CN NSLP RA Data Import TEST" mainModule="Main" version="2.0" logLevel="verbose">
<description>This project will process a file from
/mars/sftp/cn/nycboe/inbasket and
import its data into a database in PEMS.
It will then Archive the original file.</description>
<module name="Main" logLevel="verbose">
<timestamp version="1.0" />
<createWorkspace version="1.0" />
<createFileList fileListVariable="inboxFiles" version="1.0">
<fileset dir="/mars/sftp/CN/NYCBOE/inbasket">
<wildcardFilter>
<include pattern="*.*" />
</wildcardFilter>
</fileset>
</createFileList>
<forEachLoop label="CN NSLP RA Data Import" itemsVariable="${inboxFiles}" currentItemVariable="currentFile">
<if label="CN NSLP RA Data Import" condition="${inboxFiles}" />
<readFixedWidth label="Import file" inputFilesVariable="${currentFile}" outputRowSetVariable="fwImport" skipFirstRow="false" recordDelimiter="CRLF" version="1.0" executeOnlyIf="${EndsWith(String(currentFile), 'NSLP_RA_DATA.TXT')}" disabled="false" onError="call:ErrorModule">
<data trim="none">
<column index="1" name="SA_LEA_CODE" size="12" type="VARCHAR" />
<column index="2" name="RA_NAME" size="150" type="VARCHAR" />
<column index="3" name="DIST_CODE" size="2" type="NUMERIC" />
<column index="4" name="ADDRESS" size="36" type="VARCHAR" />
<column index="5" name="CITY" size="20" type="VARCHAR" />
<column index="6" name="ZIP" size="10" type="VARCHAR" />
<column index="7" name="AGENCY_TYPE" size="2" type="VARCHAR" />
<column index="8" name="RA_LEA_CODE" size="12" type="VARCHAR" />
<column index="9" name="PROGRAM" size="20" type="VARCHAR" />
<column index="10" name="SNACK_OVERRIDE" size="1" type="VARCHAR" />
<column index="11" name="METHOD_OF_SERVICE" size="16" type="VARCHAR" />
<column index="12" name="BEGIN_DATE" size="10" type="VARCHAR" />
<column index="13" name="END_DATE" size="10" type="VARCHAR" />
<column index="14" name="BEGIN_TIME" size="8" type="VARCHAR" />
<column index="15" name="END_TIME" size="8" type="VARCHAR" />
<column index="16" name="PROJ_ADP" size="7" type="NUMERIC" />
<column index="17" name="OFFER_SERVE" size="1" type="VARCHAR" />
<column index="18" name="REDUCED_PRICE" size="6" type="VARCHAR" />
<column index="19" name="PAID_PRICE" size="6" type="VARCHAR" />
<column index="20" name="NURSERY" size="1" type="VARCHAR" />
<column index="21" name="PRE_K" size="1" type="VARCHAR" />
<column index="22" name="HALF_K" size="1" type="VARCHAR" />
<column index="23" name="FULL_K" size="1" type="VARCHAR" />
<column index="24" name="FIRST" size="1" type="VARCHAR" />
<column index="25" name="SECOND" size="1" type="VARCHAR" />
<column index="26" name="THIRD" size="1" type="VARCHAR" />
<column index="27" name="FORTH" size="1" type="VARCHAR" />
<column index="28" name="FIFTH" size="1" type="VARCHAR" />
<column index="29" name="SIXTH" size="1" type="VARCHAR" />
<column index="30" name="SEVENTH" size="1" type="VARCHAR" />
<column index="31" name="EIGHTH" size="1" type="VARCHAR" />
<column index="32" name="NINTH" size="1" type="VARCHAR" />
<column index="33" name="TENTH" size="1" type="VARCHAR" />
<column index="34" name="ELEVENTH" size="1" type="VARCHAR" />
<column index="35" name="TWELFTH" size="1" type="VARCHAR" />
<column index="36" name="UNGRADED" size="1" type="VARCHAR" />
<column index="37" name="SPEC_ED" size="1" type="VARCHAR" />
<column index="38" name="CEP_ISP" size="8" type="VARCHAR" />
<column index="39" name="ADDRESS_CHANGE" size="1" type="VARCHAR" />
<column index="40" name="NAME_CHANGE" size="1" type="VARCHAR" />
<column index="41" name="PROGRAM_DELETE" size="1" type="VARCHAR" />
<column index="42" name="LEA_DEACTIVATED" size="1" type="VARCHAR" />
</data>
</readFixedWidth>
<sql resourceId="PEMS" autoCommit="false" user="cn" password="f33dk1ds" version="1.0" logLevel="debug" executeOnlyIf="${EndsWith(String(currentFile), 'NSLP_RA_DATA.TXT')}" onError="call:ErrorModule">
<query label="TRUNCATE TABLE ST_NSLP_RA_DATA">
<statement>TRUNCATE TABLE CN.ST_NSLP_RA_DATA</statement>
</query>
<query label="INSERT INTO ST_NSLP_RA_DATA" inputRowSetVariable="${fwImport}" outputVariable="Main" createScrollableRowSet="false">
<statement>INSERT INTO CN.ST_NSLP_RA_DATA (SA_LEA_CODE,RA_NAME,DIST_CODE,ADDRESS,CITY,ZIP,AGENCY_TYPE,RA_LEA_CODE,PROGRAM,SNACK_OVERRIDE,METHOD_OF_SERVICE,BEGIN_DATE,END_DATE,BEGIN_TIME,END_TIME,PROJ_ADP,OFFER_SERVE,REDUCED_PRICE,PAID_PRICE,NURSERY,PRE_K,HALF_K,FULL_K,FIRST,SECOND,THIRD,FORTH,FIFTH,SIXTH,SEVENTH,EIGHTH,NINTH,TENTH,ELEVENTH,TWELFTH,UNGRADED,SPEC_ED,CEP_ISP,ADDRESS_CHANGE,NAME_CHANGE,PROGRAM_DELETE,LEA_DEACTIVATED) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?.?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
</statement>
</query>
</sql>
</forEachLoop>
<deleteWorkspace version="1.0" />
</module>
<module name="ErrorModule">
<sendEmail label="Send Error Email" resourceId="uxmail" toList="[email protected]" version="2.0">
<from address="[email protected]" />
<subject>
<![CDATA[Project ${system.project.name} Failed ]]>
</subject>
<message>
<![CDATA[Project ${system.project.name} failed due to the following reason:
${system.job.error}]]>
</message>
<attachment file="${system.job.log}" />
</sendEmail>
<deleteWorkspace version="1.0" />
<raiseError label="Flag Job as Error" version="1.0">
<message>${system.job.error}</message>
</raiseError>
</module>
</project>