pentaho/pentaho-kettle · error · KettleException

DynamicSQLRow.Exception.IncorrectNrTemplateFields

Error message

DynamicSQLRow.Exception.IncorrectNrTemplateFields

What it means

DynamicSQLRow.lookupValues validates that the number of template fields (addMeta, the fields the SQL is expected to return) matches the number of columns the step actually appends to the output row (outputRowMeta size minus input size). If these counts differ, it throws this KettleException naming both counts and the SQL.

Solutions

  1. Reopen the Dynamic SQL Row step, re-detect the query fields so output fields match the SQL
  2. Verify the SQL template column count is stable across variable substitution
  3. Clear any cached query-field metadata and re-save the transformation
  4. Align the declared output fields in the dialog with the actual SELECT list

Example fix

// before
SELECT id, name FROM customers WHERE region = ?   // dialog still declares 3 return fields
// after
SELECT id, name, created_at FROM customers WHERE region = ?   // now matches the 3 declared fields
Defensive patterns

Strategy: validation

Validate before calling

// In the step dialog, click 'SQL' to re-detect fields after any SQL change,
// then verify counts match:
int declaredOutputFields = outputRowMeta.size() - inputRowMeta.size();
if (declaredOutputFields != templateFields.size()) {
  throw new IllegalStateException("Template/output field count mismatch: " + declaredOutputFields + " vs " + templateFields.size());
}

Try / catch

try {
  return lookupValues(inputRowMeta, row);
} catch (KettleException e) {
  logError("Template field count mismatch — re-detect SQL fields in the dialog", e);
  throw e;
}

Prevention

When it happens

Trigger: At runtime in processRow→lookupValues when the template row metadata (addMeta from getQueryFields) has a different field count than the declared output row metadata — e.g. the SQL was edited after the step metadata was generated, or 'replace variables' changed the query.

Common situations: Editing the dynamic SQL template without re-running 'SQL' detection in the step dialog; environment variable substitution producing a different column count per run; cached QueryFields from an older SQL.

Understand the failure class

Background: Schema validation failed / invalid input schema: payload rejected because its shape doesn't match the expected schema — this error's family across 28 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/bccf4989b10aa780. Report an issue: GitHub.

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/dynamicsqlrow/DynamicSQLRow.java:122

      }
    } else {
      if ( meta.isQueryOnlyOnChange() ) {
        data.previousrowbuffer.clear();
      }

      // Set the values on the prepared statement (for faster exec.)
      ResultSet rs = data.db.openQuery( sql );

      // Get a row from the database...
      Object[] add = data.db.getRow( rs );
      RowMetaInterface addMeta = data.db.getReturnRowMeta();

      // Also validate the data types to make sure we've not place an incorrect template in the dialog...
      //
      if ( add != null ) {
        int nrTemplateFields = data.outputRowMeta.size() - getInputRowMeta().size();
        if ( addMeta.size() != nrTemplateFields ) {
          throw new KettleException( BaseMessages.getString(
            PKG, "DynamicSQLRow.Exception.IncorrectNrTemplateFields", nrTemplateFields, addMeta.size(), sql ) );
        }
        StringBuilder typeErrors = new StringBuilder();
        for ( int i = 0; i < addMeta.size(); i++ ) {
          ValueMetaInterface templateValueMeta = addMeta.getValueMeta( i );
          ValueMetaInterface outputValueMeta = data.outputRowMeta.getValueMeta( getInputRowMeta().size() + i );

          if ( templateValueMeta.getType() != outputValueMeta.getType() ) {
            if ( typeErrors.length() > 0 ) {
              typeErrors.append( Const.CR );
            }
            typeErrors.append( BaseMessages.getString(
              PKG, "DynamicSQLRow.Exception.TemplateReturnDataTypeError", templateValueMeta.toString(),
              outputValueMeta.toString() ) );
          }
        }
        if ( typeErrors.length() > 0 ) {
          throw new KettleException( typeErrors.toString() );

View on GitHub (pinned to f3058517a1)