Overview


Several Semarchy xDI Templates rely on generating a temporary file that is then loaded into the target database using a native, high-performance loading command (e.g., BULK INSERT for Microsoft SQL Server, COPY for PostgreSQL, Vertica, Netezza etc...). These Templates all share the same underlying constraint, and therefore the same configuration method, regardless of the target database technology.


The Core Constraint

These native loading commands (BULK INSERT, COPY, etc.) are executed by the database engine itself, not by the xDI Runtime. This means:

  • The database engine needs direct access to the temporary file generated by xDI.
  • If the xDI Runtime and the target database are installed on the same machine, this works seamlessly — the file is already accessible.
  • If the xDI Runtime and the target database are on different machines, the temporary file must first be transferred to a location the database engine can read.

This is exactly the situation documented for SQL Server’s BULK INSERT:

Microsoft SQL Server – BULK INSERT configuration

The Method (applies across technologies)

The same pattern is used for every technology offering a file-based/native load Template (SQL Server BULK INSERT, PostgreSQL COPY, Vertica COPY, SAP Hana IMPORT FROM, etc.):


  1. Select the appropriate Template in your Mapping (the one using the native load method, e.g., “with COPY” or “with BULK INSERT”).
  2. Check where the Runtime and the database are located:
    • Same machine → no extra configuration needed, the file is already visible to the database.
    • Different machines → a file transport mechanism must be configured (see step 3).
  3. Configure automatic file transportwhen Runtime and database are on separate machines:
    • Create an FTP Metadata (or SSH Metadata, depending on the technology) pointing to the target database server.
    • Define a folder/directory with the Absolute Path set — this is where the Runtime will drop the temporary file, and where the database engine will read it from.
    • Drag and drop this FTP/SSH folder onto the target database Metadata (schema or table level).
    • Rename the linkto the expected name recognized by the Template:
      • TARGET_FTP for FTP-based transport (e.g., SQL Server, Vertica)
      • TARGET_SSH for SSH-based transport (e.g., SAP Hana)
    • In the Mapping, enable the corresponding parameter on the Template (e.g., “Enable File Transport” for SQL Server BULK INSERT; PostgreSQL/Vertica COPY Templates have an equivalent file-transport/local-mode parameter).
  4. Run the Mapping.

Applying This to PostgreSQL

For PostgreSQL Templates using COPY, the same logic applies as for SQL Server’s BULK INSERT:

  • If the xDI Runtime and PostgreSQL are on the same server, the generated working file can be used directly — just make sure the Work Folder parameter on the Template points to a directory visible to PostgreSQL.
  • If they are on separate servers, you must set up a way for PostgreSQL to access the file generated by the Runtime — either:
    • A network share/mount written to by the Runtime and readable by PostgreSQL, or
    • An FTP transfer, configured exactly as described in the BULK INSERT documentation above (FTP Metadata + folder renamed to TARGET_FTP, linked to the PostgreSQL Metadata).