pentaho/pentaho-kettle · error · KettleException

Error retrieving sqlldr string

Error message

Error retrieving sqlldr string

What it means

GPBulkLoader.builds a command line for the psql (gpload/sqlldr-style) executable. Before appending the executable path to the command line it resolves meta.getPsqlpath() through environmentSubstitute() and KettleVFS.getFileObject(). If any of that throws (bad VFS provider, unresolved variable, invalid path syntax, VFS IO error), it is wrapped in this KettleException. It is a wrapper exception: the cause holds the real problem.

Solutions

  1. Inspect the wrapped cause (ex.getCause()) and log/fix the underlying VFS or variable resolution failure.
  2. Define every environment variable referenced in the psql path (e.g. set ${PENTAHO_PSQL_PATH} in kettle.properties or the transformation's parameters) so environmentSubstitute() resolves it.
  3. Verify the psql path in the GP Bulk Loader step dialog is a plain absolute path or a supported VFS URI, e.g. /usr/local/greenplum-db/bin/psql.
  4. Confirm the file exists and is readable by the user running Kettle; test the path in a file browser before pasting it in.

Example fix

// before: unresolved variable in step setting
psql path = ${GP_HOME}/bin/psql   (GP_HOME not defined)

// after: define it in kettle.properties or as a transformation parameter
GP_HOME=/usr/local/greenplum-db
psql path = ${GP_HOME}/bin/psql
Defensive patterns

Strategy: validation

Validate before calling

String psqlPath = meta.getPsqlpath();
if (psqlPath == null || psqlPath.isEmpty())
  throw new IllegalArgumentException("psql path not set");
String resolved = transformation.environmentSubstitute(psqlPath);
if (resolved.contains("${"))
  throw new IllegalArgumentException("Unresolved variable in psql path: " + psqlPath);
if (!new java.io.File(resolved).canExecute())
  throw new IllegalArgumentException("psql not executable at: " + resolved);

Try / catch

try {
  execute(meta, wait);
} catch (KettleException e) {
  if (e.getMessage().contains("Error retrieving sqlldr string")) {
    logError("psql path resolution failed", e.getCause());
    // fix variable/path then retry
  } else throw e;
}

Prevention

When it happens

Trigger: createCommandLine() is called from execute() when the psql path field is set (non-null) but resolving it fails: environmentSubstitute finds no value for a ${VAR} used inside the path, the VFS scheme is unknown/unsupported (e.g. sftp without a provider), or KettleVFS.getFilename(fileObject) fails on the resolved FileObject.

Common situations: psql path contains an environment variable that is not defined at runtime; the path uses a VFS URI scheme unavailable in this Pentaho/Kettle installation; the field was filled from a saved transformation whose variable scope does not include the variable; typo in the path so VFS throws while resolving.

Understand the failure class

Background: "File not found" and ENOENT errors: why libraries can't find a file that should exist — this error's family across 50 libraries.

Related errors

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

Appendix: source

Thrown at plugins/gp-bulk-loader/core/src/main/java/org/pentaho/di/trans/steps/gpbulkloader/GPBulkLoader.java:319

   *          Use the real password or not
   *
   * @return The string to execute.
   *
   * @throws KettleException
   *           Upon any exception
   */
  public String createCommandLine( GPBulkLoaderMeta meta, boolean password ) throws KettleException {
    StringBuffer sb = new StringBuffer( 300 );

    if ( meta.getPsqlpath() != null ) {
      try {
        FileObject fileObject =
          KettleVFS.getInstance( getTransMeta().getBowl() )
            .getFileObject( environmentSubstitute( meta.getPsqlpath() ), getTransMeta() );
        String psqlexec = KettleVFS.getFilename( fileObject );
        sb.append( enclosure ).append( psqlexec ).append( enclosure );
      } catch ( Exception ex ) {
        throw new KettleException( "Error retrieving sqlldr string", ex );
      }
    } else {
      throw new KettleException( "No psql application specified" );
    }

    if ( meta.getControlFile() != null ) {
      try {
        FileObject fileObject =
          KettleVFS.getInstance( getTransMeta().getBowl() )
            .getFileObject( environmentSubstitute( meta.getControlFile() ), getTransMeta() );

        sb.append( " -n -f " );
        sb.append( enclosure ).append( KettleVFS.getFilename( fileObject ) ).append( enclosure );
      } catch ( Exception ex ) {
        throw new KettleException( "Error retrieving controlfile string", ex );
      }
    } else {
      throw new KettleException( "No control file specified" );

View on GitHub (pinned to f3058517a1)