Table input

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

The Table Input step is used to run a select statement against the data flow’s upsert table to determine the latest DL Document Date loaded to the specified table. This information is passed on to the DataLakeInput step and automatically added to the query string. At the same time the payload records are selected that must be pulled and sent to the on-premise 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:02' AS datetime), 127))+'Z'
  END as queryDlDocumentDate
FROM <tablename>
  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.