Problem using RowErrFileName

[Gerardo_Led]

Hi,

I've been trying to implent a TPT using the feature RowErrFileName but it does't seems to work, when I load a Input File with some errors like incomplete columns it doesn't send the bad rows to the 'RowErrFileName' instead of this the TPT goes to FatalError and crashes.

Any Help?

This is how I am writing the TPT DataConnector Operator

DEFINE OPERATOR CONECTOR_PRODUCER
DESCRIPTION 'DataConnector'
TYPE DATACONNECTOR PRODUCER
SCHEMA PnrItineraries_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconector_log' ,
VARCHAR OpenMode = 'Read' ,
VARCHAR Format = 'Delimited' ,
VARCHAR DirectoryPath = 'C:\ETL_DEV\Files' ,
VARCHAR FileName = 'ITINERARY.*' ,
VARCHAR ArchiveDirectoryPath = 'C:\ETL_DEV\Files\Success',
VARCHAR RowErrFileName = 'C:\ETL_DEV\Files\Failed' ,
VARCHAR TextDelimiter = '|'
);

Thank You

[feinholz]

Please be more specific about "TPT goes to FatalError and crashes".
Please provide a snapshot of the output you are seeing.
Please also provide the version of TPT that you are using.

[Gerardo_Led]

Sorry,

By "TPT goes to FatalError and crashes" I mean that if there is more than one file and one of this has no errors and the other has, TPT with the feature 'RowErrFileName' must load at least the file with no errors, and send the bad rows of the other file into a new file in 'C:\ETL_DEV\Files\Failed' am I right? Maybe I'm misunderstanding the feature.

Because the TPT does not load anything into the target table.

The version of the TPT that I'm using is 13.10.00.02

Here is the output that I'm seeing. Thanks in advance.

Teradata Parallel Transporter SQL DDL Operator Version 13.10.00.02
DDL_OPERATOR: private log specified: ddl_log
DDL_OPERATOR: connecting sessions
DDL_OPERATOR: sending SQL requests
DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'trabajo.PnrItineraries_Log' does not exist.
DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList
DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'trabajo.PnrItineraries_ET' does not exist.
DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList
DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'trabajo.PnrItineraries_UV' does not exist.
DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList
DDL_OPERATOR: disconnecting sessions
DDL_OPERATOR: Total processor time used = '0.21875 Second(s)'
DDL_OPERATOR: Start : Thu Aug 11 08:20:13 2011
DDL_OPERATOR: End : Thu Aug 11 08:20:15 2011
Job step A_Setup_Tables completed successfully
Teradata Parallel Transporter Load Operator Version 13.10.00.02
LOAD_OPERATOR: private log not specified
Teradata Parallel Transporter DataConnector Version 13.10.00.02
CONECTOR_PRODUCER Instance 2 directing private log report to 'dataconector_log-2'.
CONECTOR_PRODUCER Instance 1 directing private log report to 'dataconector_log-1'.
CONECTOR_PRODUCER Instance 4 directing private log report to 'dataconector_log-4'.
CONECTOR_PRODUCER Instance 3 directing private log report to 'dataconector_log-3'.
CONECTOR_PRODUCER: TPT19008 DataConnector Producer operator Instances: 4
CONECTOR_PRODUCER: TPT19003 ECI operator ID: CONECTOR_PRODUCER-5904
CONECTOR_PRODUCER: TPT19222 Operator instance 2 processing file 'C:\ETL_DEV\Files\ITINERARY2.txt'.
LOAD_OPERATOR: connecting sessions
CONECTOR_PRODUCER: TPT19222 Operator instance 1 processing file 'C:\ETL_DEV\Files\ITINERARY1.txt'.
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: TPT10508: RDBMS error 3803: Table 'ET_Trans' already exists.
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '0.75 Second(s)'
LOAD_OPERATOR: Start : Thu Aug 11 08:20:22 2011
LOAD_OPERATOR: End : Thu Aug 11 08:20:29 2011
CONECTOR_PRODUCER: TPT19221 Total files processed: 0.
Job step Load_Trans_Table terminated (status 12)
Job gl255000 terminated (status 12)

[feinholz]

For this particular output you sent me, the job terminated because of this:

LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: TPT10508: RDBMS error 3803: Table 'ET_Trans' already exists.
LOAD_OPERATOR: disconnecting sessions
LOAD_OPERATOR: Total processor time used = '0.75 Second(s)'
LOAD_OPERATOR: Start : Thu Aug 11 08:20:22 2011
LOAD_OPERATOR: End : Thu Aug 11 08:20:29 2011

This means that when you started a new job, one of the error tables already existed (possibly from another previously run job).
If you are trying to start a job from the beginning, please make sure that all error tables and log tables do not exist.
In this case, the job is complaining about the existence of a table called "ET_Trans".

[Martijn]

Hi, I am having the same problem. When using RowErrFileName i was hoping the file which had the error in it got skipped (or the row got skipped) and the tpt process just went to the next file. However the script stops and does not continue to load other files.

Here is my script:

DEFINE JOB load_amr(

/******************************************************/

 DEFINE OPERATOR W_1_op_load_amr

 TYPE LOAD

 SCHEMA *

 ATTRIBUTES

 (

  VARCHAR UserName, VARCHAR UserPassword, VARCHAR LogTable, VARCHAR TargetTable,  INTEGER BufferSize, INTEGER ErrorLimit, INTEGER MaxSessions, INTEGER MinSessions,

  INTEGER TenacityHours, INTEGER TenacitySleep, VARCHAR AccountID, VARCHAR DateForm,

  VARCHAR ErrorTable1, VARCHAR ErrorTable2, VARCHAR NotifyExit, VARCHAR NotifyExitIsDLL,

  VARCHAR NotifyLevel, VARCHAR NotifyMethod, VARCHAR NotifyString, VARCHAR PauseAcq,

  VARCHAR PrivateLogName, VARCHAR TdpId, VARCHAR TraceLevel, VARCHAR WorkingDatabase

 );

/******************************************************/

 DEFINE OPERATOR DDL_OPERATOR()

 TYPE DDL

 ATTRIBUTES

 (

  VARCHAR TdpId ='vm',

   VARCHAR UserName ='d1_cdr_loader',

   VARCHAR UserPassword = 'd1',

   VARCHAR PrivateLogName = 'ddllog',

   VARCHAR ErrorList = '3807'

 );

/******************************************************/

 DEFINE SCHEMA W_0_sc_load_amr

 (

     AMR_Profile VARCHAR(255),

 AMR_Date       VARCHAR(255),

 AMR_Time VARCHAR(255),

 AMR_Value VARCHAR(255),

 AMR_Unit VARCHAR(255),

 AMR_Status VARCHAR(255),

 AMR_UTC_Offset VARCHAR(255)

 );

/******************************************************/

 DEFINE OPERATOR W_0_op_load_amr

 TYPE DATACONNECTOR PRODUCER

 SCHEMA W_0_sc_load_amr

 ATTRIBUTES

 (

  VARCHAR ArchiveDirectoryPath,

  VARCHAR EnableScan,

  VARCHAR FileName,

   VARCHAR Format, VARCHAR OpenMode, INTEGER BlockSize,

    VARCHAR RowErrFileName,

  INTEGER BufferSize, INTEGER RetentionPeriod, INTEGER RowsPerInstance, INTEGER SecondarySpace,

  INTEGER UnitCount, INTEGER VigilElapsedTime, INTEGER VigilWaitTime, INTEGER VolumeCount,

  VARCHAR AccessModuleName, VARCHAR AccessModuleInitStr,

  VARCHAR DirectoryPath, VARCHAR ExpirationDate,

  VARCHAR IndicatorMode, VARCHAR PrimarySpace, VARCHAR PrivateLogName, VARCHAR RecordFormat,

  VARCHAR RecordLength, VARCHAR SpaceUnit, VARCHAR TextDelimiter, VARCHAR VigilNoticeFileName,

  VARCHAR VigilStartTime, VARCHAR VigilStopTime, VARCHAR VolSerNumber, VARCHAR UnitType

 );

/******************************************************/

 STEP SETUP_TABLES

 (

  APPLY

  ('drop table D1_STG.AMR; '),

  ('drop table D1_STG.AMR_e1; '),

  ('drop table D1_STG.AMR_e2; '),

  ('drop table D1_STG.AMR_log; '),

  ('CREATE MULTISET TABLE d1_stg.amr ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

 AMR_Profile VARCHAR(255),

 AMR_Date       VARCHAR(255),

 AMR_Time VARCHAR(255),

 AMR_Value VARCHAR(255),

 AMR_Unit VARCHAR(255),

 AMR_Status VARCHAR(255),

 AMR_UTC_Offset  VARCHAR(255) 

     )

 PRIMARY INDEX ( AMR_Profile, AMR_Date, AMR_Time );

  ')

  TO OPERATOR (DDL_OPERATOR());

 );

/******************************************************/

 STEP LOAD_TABLE

 (

 APPLY

   ('INSERT INTO D1_STG.AMR(

 AMR_Profile,

 AMR_Date,

 AMR_Time,

 AMR_Value,

 AMR_Unit,

 AMR_Status,

 AMR_UTC_Offset

    )

    VALUES

    (

 :AMR_Profile,

 :AMR_Date,

 :AMR_Time,

 :AMR_Value,

 :AMR_Unit,

 :AMR_Status,

 :AMR_UTC_Offset

   );'

  )

 TO OPERATOR

 (

  W_1_op_load_amr[1]

  ATTRIBUTES

  (

   UserName = 'd1_cdr_loader',

   UserPassword = 'd1',

   ErrorTable2 = 'd1_stg.amr_e2',

   ErrorTable1 = 'd1_stg.amr_e1',

   LogTable = 'd1_stg.amr_log',

   TargetTable = 'd1_stg.amr',

   MaxSessions = 20,

   TdpId = 'vm'

  )

 )

 SELECT

 AMR_Profile,

 AMR_Date,

 AMR_Time,

 AMR_Value,

 AMR_Unit,

 AMR_Status,

 AMR_UTC_Offset

 FROM OPERATOR

 (

  W_0_op_load_amr[1]

  ATTRIBUTES

  (

   ArchiveDirectoryPath='/opt/cdr/d1/ARC/',

   DirectoryPath='/opt/cdr/d1/DAT/amr/',

   EnableScan='Y',

   FileName = '*.csv',

   Format = 'DELIMITED',

   RowErrFileName = 'amrErrors.txt',

   OpenMode = 'Read',

   IndicatorMode = 'N',

  /* AccessModuleInitStr = '-inst_num 1 -inst_count 1 -delimiter x7e -indicdata Y -file_name datafile -pread N -quoted_strings " ' */

  /* AccessModuleName = '/usr/lib/libamrsock.so', */

   TextDelimiter = ';'

  )

 );

/******************************************************/

); );

And here is the output of the script:

Teradata Parallel Transporter Version 13.10.00.03

Job log: /opt/teradata/client/13.10/tbuild/logs/d1_cdr_loader-104.out

Job id is d1_cdr_loader-104, running on s10-1310

Teradata Parallel Transporter SQL DDL Operator Version 13.10.00.03

DDL_OPERATOR: private log specified: ddllog

DDL_OPERATOR: connecting sessions

DDL_OPERATOR: sending SQL requests

DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'D1_STG.AMR_e1' does not exist.

DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'D1_STG.AMR_e2' does not exist.

DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

DDL_OPERATOR: TPT10508: RDBMS error 3807: Object 'D1_STG.AMR_log' does not exist.

DDL_OPERATOR: TPT18046: Warning: error is ignored as requested in ErrorList

Job step SETUP_TABLES completed successfully

DDL_OPERATOR: disconnecting sessions

DDL_OPERATOR: Total processor time used = '0.08 Second(s)'

DDL_OPERATOR: Start : Sun Nov 13 13:11:16 2011

DDL_OPERATOR: End   : Sun Nov 13 13:11:42 2011

Teradata Parallel Transporter DataConnector Version 13.10.00.03

Teradata Parallel Transporter Load Operator Version 13.10.00.03

W_1_op_load_amr: private log not specified

W_0_op_load_amr: TPT19008 DataConnector Producer operator Instances: 1

W_0_op_load_amr: TPT19003 ECI operator ID: W_0_op_load_amr-14127

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120001000001_20110812_01.csv'.

W_1_op_load_amr: connecting sessions

W_1_op_load_amr: preparing target table

W_1_op_load_amr: entering Acquisition Phase

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120101000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000006_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000007_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000008_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120701000009_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120801000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120801000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120801000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120801000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120801000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000006_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000007_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000008_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000009_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108120901000010_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000006_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000007_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121001000008_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121100000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121100000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121100000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121100000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121100000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121201000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121301000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121301000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121401000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121401000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121401000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000002_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000003_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000004_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000005_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000006_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121501000007_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108121825000001_20110812_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130000000001_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130101000001_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000001_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000002_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000003_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000004_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000005_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000006_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000007_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130701000008_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130801000001_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130801000002_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108130801000003_20110813_01.csv'.

W_0_op_load_amr: TPT19222 Operator instance 1 processing file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108131144000001_20110813_01.csv'.

W_0_op_load_amr: TPT19350 I/O error on file '/opt/cdr/d1/DAT/amr/BEL_EBLEDM_201108131144000001_20110813_01.csv'.

W_0_op_load_amr: TPT19435 pmRead failed. EOF encountered before end of record (35)

W_0_op_load_amr: TPT19305 Fatal error reading data.

W_0_op_load_amr: TPT19221 Total files processed: 66.

W_1_op_load_amr: disconnecting sessions

W_1_op_load_amr: Total processor time used = '1.12 Second(s)'

W_1_op_load_amr: Start : Sun Nov 13 13:11:45 2011

W_1_op_load_amr: End   : Sun Nov 13 13:14:13 2011

Job step LOAD_TABLE terminated (status 12)

Job d1_cdr_loader terminated (status 12)

As you can see the tpt script stops at the file with the problem in it (the problem is that the last line of the file misses some delimiters).

I can work around it by editing the file in question and adding a space behind the last delimiter or by adding some of the missing delimiters. After that I can restart the script. Now all the files will be read again which takes some time.

Another workaround is ofcourse to load the files 1 by 1 via a bash shell script but that takes very long too, because then the tpt script needs perform every step in the load script for every file. So that would be an alternative.

However I have a lot more files to load, so it would be nice to be able to just load all the correct files, let the script skip the bad files and only having to reload the bad files after correcting them.

Maybe I am missing the correct purpose of the RowErrFileName ?

[feinholz]

Ok, a few things.

First, if you are reading all of those files (you used the wildcard *.csv) you should be specifying more than 1 instance of the DataConnector operator so that you can get these files read in parallel, instead of serially.

Next, if the CSV files only have 7 columns of information, you can do "SELECT *" in the script instead of "SELECT AMR_Profile,

 AMR_Date,

 AMR_Time,

 AMR_Value,

 AMR_Unit,

 AMR_Status,

 AMR_UTC_Offset . . ."

Next, I think the job terminated because of this:

W_0_op_load_amr: TPT19435 pmRead failed. EOF encountered before end of record (35)

This tells me that the last record in the file did not have a newline character before the EOF was reached (it was not a complete record).

(I thought we fixed that, but I will have to check with the developers).

The parsing of CSV files relies on the "end-of-record" marker to indicate the end of the record.

This type of error is probably treated differently than just a different type of record that does not have the same number of columns as the schema.

[Martijn]

Any news from your check with the developers?

It would be nice if this error would also been treated the same, so that the file would be skipped. This way the file could be corrected aftwerwards and reloaded again, without having to reload the files that went allright.

[feinholz]

Upgrade to the latest patch for 13.10, which should be 13.10.00.07.

That should fix this problem.

[emilwu]

RowErrFileName not quite working as it is explained int he documentation. the file reader quit right after hitting the error row  (row #2 with extra delimiter)

Here is what I got in the public log

Task(SELECT_2[0001]): checkpoint completed, status = Success

Task(APPLY_1[0001]): checkpoint completed, status = Success

FILE_READER: TPT19104 VARCHAR field length error in column 7 .  Received 10 (max 1) on record 2.

TPT_INFRA: TPT02352: Error: Column length does not match the length in schema

  (possible invalid input data), status = Length Error

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 1

TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

FILE_READER: TPT19221 Total files processed: 0.

CONSUMER_OPERATOR: disconnecting sessions

FILE_READER 1 error rows sent to error file /home/myuser/errorfile.err

Here is the one in the private log

    Log will include stats only

     Operator 'dtacop' main source version:'13.10.00.28'

**** 12:46:50 From file 'claim_common_1', starting to send rows.

     VARCHAR field length error on record 2, column 7. Received 10 (max 1).

     Record saved in file '/home/myuser/errorfile.err', processing continuing

     PX_PutRow returned code 29

     returning 48 (PXSTC_FatalError)

     Setting exit code = 12.

     Files read by this instance: 0

**** 12:47:05 Total processor time used = '0.00 Seconds(s)'

**** 12:47:05 Total files processed: 0

Then the loader private log

 ===================================================================

     =                                                                 =

     =                        Acquisition Phase                        =

     =                                                                 =

     ===================================================================

**** 12:47:03 Number of records per buffer for this job: 50

**** 12:47:03 Number of rows sent, last job: 0

     ===================================================================

     =                                                                 =

     =                        Logoff/Disconnect                        =

     =                                                                 =

     ===================================================================

**** 12:47:05 Logging off all sessions

              Instance      Cpu Time

              ========  ================

                   1        0.37 Seconds

[nba2006]

I am trying to load XML File using TPT and below is the script....

DEFINE JOB LOADING_LOB_DATA

DESCRIPTION 'LOAD PRODUCT DEFINITION TABLE'

(

DEFINE SCHEMA SOURCE_SCHEMA

DESCRIPTION 'PRODUCT INFORMATION SCHEMA'

(

     COL2_CLOB CLOB(50000)

);

DEFINE OPERATOR FILE_READER ()

TYPE DATACONNECTOR PRODUCER

SCHEMA SOURCE_SCHEMA

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconnector_log',

VARCHAR DirectoryPath = 'G:\Users\YashNeil\Desktop\VZ',

VARCHAR FileName = 'VZVOD_999999_20120412_100510_000000004.xml',

VARCHAR Format = 'unformatted',

VARCHAR OpenMode = 'Read'

);

DEFINE OPERATOR SQL_INSERTER ()

TYPE INSERTER

INPUT SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'inserter_log',

VARCHAR TdpId = 'localtd',

VARCHAR UserName = 'tduser',

VARCHAR UserPassword = 'tduser'

);

STEP Loading_LOB_Data

(

APPLY

('INSERT INTO retail.stage values (:Col2_CLOB);')

TO OPERATOR (SQL_INSERTER [1])

SELECT * FROM OPERATOR (FILE_READER());

);

);

I am gettting below error for this script...

Teradata Parallel Transporter Version 13.00.00.02

Job log: G:\Program Files\Teradata\Client\13.0\Teradata Parallel Transporter\/l

ogs/YashNeil-49.out

Job id is YashNeil-49, running on YashNeil-PC

Found CheckPoint file: G:\Program Files\Teradata\Client\13.0\Teradata Parallel T

ransporter\/checkpoint\YashNeilLVCP

This is a restart job; it restarts at step Loading_LOB_Data.

Teradata Parallel Transporter SQL Inserter Operator Version 13.00.00.02

SQL_INSERTER: private log specified: inserter_log

Teradata Parallel Transporter DataConnector Version 13.00.00.02

FILE_READER Instance 1 directing private log report to 'dataconnector_log-1'.

FILE_READER Instance 1 restarting.

FILE_READER DataConnector Producer operator Instances: 1

SQL_INSERTER: connecting sessions

FILE_READER Operator instance 1 processing file 'G:\Users\YashNeil\Desktop\VZ\VZ

VOD_999999_20120412_100510_000000004.xml'.

FILE_READER: TPT19003 !ERROR! LOB column data length error on record 1

FILE_READER Total files processed: 0.

SQL_INSERTER: Total Rows Sent To RDBMS:      0

SQL_INSERTER: Total Rows Applied:            0

SQL_INSERTER: disconnecting sessions

SQL_INSERTER: Total processor time used = '0.280802 Second(s)'

SQL_INSERTER: Start : Tue May 08 20:56:57 2012

SQL_INSERTER: End   : Tue May 08 20:56:59 2012

Job step Loading_LOB_Data terminated (status 12)

Job YashNeil terminated (status 12)

Could someone help me in fixing this problem...