How to fix TPT script error? 'MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation' '

Question

I am using Teradata Parallel Transport to select and insert data from tables in a SQL Server to identical tables in a Teradata database. I have done this before successfully but this is the first time I've seen this error when running the script through command line I get this output:

TD_LOADER: connecting sessions
TD_LOADER: preparing target table(s)
TD_LOADER: entering DML Phase
TD_LOADER: entering Acquisition Phase
ODBC_READER: sending SELECT request
ODBC_READER: TPT17107: Retrieval error for row: 1
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,
MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation'
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,type here

A section of the .out file generated by the script shows:

TD_LOADER: connecting sessions
TD_LOADER: preparing target table(s)
TD_LOADER: entering DML Phase
TD_LOADER: entering Acquisition Phase
Job step 'load_the_data' uses the max row size 1024KB.
Job step 'load_the_data' uses the max Using Data Bytes: 1023991.
Job step 'load_the_data' allows data source containing rows that exceed the maximun 
length of 1024KB
Task(APPLY_1[0001]): checkpoint completed, status = Success
Task(SELECT_2[0001]): checkpoint completed, status = Success
ODBC_READER: sending SELECT request
ODBC_READER: TPT17107: Retrieval error for row: 1
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,
MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation' 
ODBC_READER: TPT17101: Fatal error received from ODBC driver:
STATE=01004, CODE=0,

All of my TPT scripts follow this template:

USING CHAR SET ASCII
DEFINE JOB JobName
DESCRIPTION 'Load data from SQL(tablename) to Teradata(tablename)'


(
  DEFINE OPERATOR Database_Commands()
  TYPE DDL
  ATTRIBUTES
  (
    VARCHAR TdpId = @Teradata_Connection,
    VARCHAR UserName = @Teradata_UserId,
    VARCHAR UserPassword = @Teradata_Password,
    VARCHAR Array ErrorList = ['2580'],
    VARCHAR PrivateLogName = 'ddllog.txt',
    VARCHAR Tracelevel='None',
    VARCHAR LogSQL = ''
 );
  
  /*Examples*/
  DEFINE SCHEMA SourceTableSchema
  (
     columnname      INTEGER,
     columnname2     VARCHAR(50),
     columnname3     VARCHAR(85),
     columnname4     VARCHAR(15),
     columnname4     BYTEINT    
  );
  
  
  DEFINE SCHEMA TargetTableSchema
  (
     columnname      INTEGER,
     columnname2     VARCHAR(50),
     columnname3     VARCHAR(85),
     columnname4     VARCHAR(15),
     columnname4     BYTEINT     
  );
  

  DEFINE OPERATOR ODBC_READER
  TYPE ODBC
  SCHEMA SourceTableSchema
  ATTRIBUTES
  (
      VARCHAR PrivateLogName = 'odbc_log.txt',  
      VARCHAR dsnName = 'SQLDatabaseName',                              
      VARCHAR UserName = @ODBC_UserId,        
      VARCHAR UserPassword = @ODBC_Password,
      VARCHAR TraceLevel = 'None',
      VARCHAR SelectStmt = @SQL 
  );
  
   
  DEFINE OPERATOR TD_LOADER()
  TYPE UPDATE
  SCHEMA TargetTableSchema
  ATTRIBUTES
  (
    VARCHAR TargetTable = @TargetTable,
    VARCHAR LogTable    = @ErrDatabase||'.'||@TargetTable||'_LT', 
    VARCHAR WorkTable   = @WorkDatabase||'.'||@TargetTable||'_WT',
    VARCHAR ErrorTable1 = @ErrDatabase||'.'||@TargetTable||'_ET',
    VARCHAR ErrorTable2 = @ErrDatabase||'.'||@TargetTable||'_UV',
    VARCHAR DateForm = 'IntegerDate',
    VARCHAR TdpId = @Teradata_Connection,
    VARCHAR UserName = @Teradata_UserId,
    VARCHAR UserPassword = @Teradata_Password,
    VARCHAR WorkingDatabase = @TargetDatabase,
    VARCHAR PrivateLogName = 'td_loader_log.txt'
  );

STEP RELEASE
  (
     APPLY
           ' release mload '|| @TargetDatabase ||'.'||@TargetTable ||'  ;'
     TO OPERATOR (Database_Commands());
   );


STEP Initialize
  (
     APPLY
           ' delete from '|| @TargetDatabase ||'.'||@TargetTable ||' '||@DeleteScope||' ;'
     TO OPERATOR (Database_Commands());
   );


STEP load_the_data
  (
    APPLY 
         ('INSERT INTO '|| @TargetDatabase ||'.'||@TargetTable||'
                            (   
                           
                             ...columnnames...

                            );'
          )
    TO OPERATOR (TD_LOADER)
       SELECT                      
                                   
              ...columnnames...
    
         FROM OPERATOR (ODBC_READER); 
  );
);

I tried checking the table definitions for both the target and the source tables but since the target tables were created using the definitions of the source tables, they are identical so I am not sure what the sizing issue is between them.

Answer 1

I set the value for VARCHAR SelectStmt = @SQL in a separate file. In this file I had to change the select statement from one that selects all, to a statement that explicitly selects each row individually and converts NVARCHAR columns into VARCHAR columns (as @Fred suggested).

CAST(SourceColumnName AS VARCHAR(LengthofTargetColumn)) AS TargetColumnName