Variables passed to Kettle

All variables that are passed to Kettle are of type String. Number variables are converted by the custom workflow task to type String. The TRIRIGA® field types that are supported in Kettle are Text, Boolean, Date, Date and Time, Locators, and Numbers.

Table 1. The following are example fields on the ETL Job Item:
Field name Field label Field type
triActiveEndDA Active End Date Date
triActiveStartDA Active Start Date Date
triBONamesTX BO Names Text
triControlNumberCN Control Number Control Number
triCreatedByTX Created By Text
triLocator triLocator Text
triModuleNamesTX Module Names Text
triNameTX Name Text
triTransformBI Transform File Binary
Table 2. The following are the variables passed to Kettle:
Variable that is passed to Kettle Description
triNameTX (Text)
triActiveStartDA (Number) date in milliseconds since January 1, 2014
triActiveStartDA_DATE (Date) wrapped if Oracle or DB2, time is whatever it was on the attribute
triActiveStartDA_MinDATE (Date) wrapped if Oracle or DB2, time is 00:00:00
triActiveStartDA_MaxDATE (Date) wrapped if Oracle or DB2, time is 23:59:59
triActiveStartDA_Min (Number) date in milliseconds since January 1, 2014, time is 00:00:00
triActiveStartDA_Max (Number) date in milliseconds since January 1, 2014, time is 23:59:59
triActiveEndDA (Number) date in milliseconds since January 1, 2014
triActiveEndDA_DATE (Date) wrapped if Oracle or DB2, time is whatever it was on the attribute
triActiveEndDA_MinDATE (Date) wrapped if Oracle or DB2, time is 00:00:00
triActiveEndDA_MaxDATE (Date) wrapped if Oracle or DB2, time is 23:59:59
triActiveEndDA_Min (Number) date in milliseconds since January 1, 2014, time is 00:00:00
triActiveEndDA_Max (Number) date in milliseconds since January 1, 2014, time is 23:59:59
triActiveEndDA (Number) date in milliseconds since January 1, 2014
triCreatedByTX (Text)
triRunDATE (Number) Run Date set by Custom workflow task
triLocator (Text – Locator) is a locator field that contains a reference to another business object. This variable contains the text value of that record’s field
triLocator_IBS_SPEC (Text - Locator) contains the spec_id of the record in the triLocator field. You can use this spec_id to find information related that record through other database tables

triControlNumberCN and triTransformBI are not passed to Kettle.

Important: Things to remember about variables:
  • There are six variables for each Date, and Date and Time, type field. TRIRIGA wraps the value and hands it to Kettle in six different formats.
  • Variables in Kettle are all strings. If you need a variable to be a number in the script, you need to use a conversion. You can set a number field like, for example, TRICREATEDDA, with a variable like, for example, triRunDATE. Kettle does some implicit conversions, but if you want to do any calculations with a variable, you must first convert the variable to a number.
  • For dates, you must use the correct representation. For example, you cannot include spec.UPDATED_DATE >= ${triCreatedDA} in your selection. spec.UPDATED_DATE is a date while triCreatedDA is a number. The results are inaccurate or the SQL fails.
  • The attribute types supported to pass to Kettle are limited to Text, Boolean, Date, Date and Time, and Numbers. All other TRIRIGA data types are skipped (except Locators).
  • For Locator fields, two variables are created, one for the text of the Locator and the other for the SPEC_ID of the linked record. You can use the SPEC_ID to find information that is related to that record through other database tables.