Table input

This setup is Infor’s best practice with the Data Lake API (payload extraction) process to deliver data through the ETL Client to on-premises databases.

The Table Input step executes a select statement against the data flow’s upsert table to determine the latest dlDocumentDate (indexed data of the data object) loaded into the specified table. This information is passed on to the Data Lake Input step and automatically added to the query string to enable an incremental extraction process. At the same time, the payload records are selected that must be retrieved and sent to the on-premises database.

  1. Click Design > Input.
  2. Double-click or drag the Table Input step, to the transformation tab in the right-hand pane.
  3. Double-click the Table input step to open the Table Input dialog box.
  4. Specify this information:
    Step Name
    Update the name of the step that indicates which table’s DL Document Date is evaluated, for example: Get_DLDocDate_CSYCAL.
    Connection
    Configure a New Connection, click New to establish the Staging / Target DB connection.
    Connection Name
    Keep the name generic in nature, which helps during the promotion the transformations to other environments.
    Connection Type
    Select a connection type from the list.
    Settings
    Staging / Target Database Credentials - manually specify the details or use the environment variables in the kettle.properties file. To add a variable, press Ctrl + Space and a list of environment variable are shown.

    If the Instance Name field does not allow the Ctrl + Space functionality then when using an instance instead of Port, specify ${DB_INSTANCE} in the field.

  5. Click Test and ensure the connection is successful and click OK.
  6. In the table input dialog box update the SQL statement: 
    Select CASE
  WHEN MAX(dlDocumentDate) IS NULL 
     THEN '${DATALAKE_DEFAULT_STARTDATE}'
    ELSE (CONVERT(VARCHAR(33), CAST(max(dlDocumentDate) as datetime) - CAST('00:00:02' AS datetime), 127))+'Z'
  END as queryDlDocumentDate
FROM <tablename>
    This SQL extracts the most recent dlDocumentDate and subtracts 2 seconds from the time stamp and passes the new value to the Data Lake Input step to perform the next extraction. In case you are using an older version of the ETL Client (prior to 2022.08) that are not utilizing the data object’s indexed time stamp, it is recommended that you change the 2 seconds to 2 minutes to be subtracted from the dlDocumentDate.
  7. Select the Replace variable in script? check box.
    The preview does not work until the StagingUpsert step is completed where the on-premises table is created.
  8. Click OK to finish the Table Input step.