Runtime Variables

When jobs execute, a number of variables are automatically defined and may be available throughout the job execution. These variables are automatically set and can be used in different ways. In some cases, variables are available only for the read operation, while others are only available during the execution of the job writer. Runtime variables include date tokens that can be used on target names, such as file names, to distribute data automatically.

Variables names are case-sensitive. For example, to specify a dynamic database table name that uses the execution Id of a job, you can name the target object as such: mydatabase.dbo.[target-tmp-@executionid]

When to use Runtime Variables

Job variables can be used directly as part of object names, scripts, in many data pipeline components, and may be combined with DataZen functions. Here is a summary of where to use runtime variables:

  • Job Reader Specification: You can use some of these variables in the Job Reader specification, such as when calling an HTTP URI, in the HTTP Payload and in Database SQL Scripts. For example, read from a database table using the last saved/known @highwatermark value: 
    SELECT * FROM mytable WHERE timestamp > @highwatermark
  • Job Writer Specification: Many of the runtime variables can be accessed as part of the Job Writer specification. You can, for example, use the execution ID of a job to create a unique, log-specific target file in the cloud, so that it can be processed further downstream individually. In this case, you could name the target file as such: 
    newcustomers-@executionid.json
  • Data Pipelines (reader/writer): In the context of data pipelines, runtime variables can be used in components that allows you to specify a script, or an object name (such as an HTTP URL). For example, you may want to sink the current state of the pipeline data set, and specify a dynamic name that includes both the source job and the execution id: 
    stage-@guid-@executionid.json
  • SQL Completion Scripts: When using a database as the target, your may want to execute a custom finilization script, which is limited to SQL Server. This feature has access to an extra runtime variable: status. You could use this to drive the finalization script: 
    if (@status == 'failure') BEGIN INSERT INTO warnings (jobkey, rundate, changelog, msg) VALUES ('@jobkey', '@rundate', '@packagefile', 'Error detected!')

Job Variables

The following table provides a summary of runtime variables related to jobs:

Variable Description Job Reader Job Writer
batchid A unique identifier generated for each batch of write operations, when applicable (ex: 500 records are pushed, with Max Batch Count = 100 ==> 5 separate batches will be created). This variable can be directly accessed within the SQL script. X
executionId The Unix timestamp in milliseconds of the execution start time of a job; this value is unique for a given job. X X
guid The unique GUID identifier of the job; unless the job is recreated, this value remains unchanged for a given job. X X
packagefile The name of the change log being applied, or replayed, against the target system X
jobkey The name of the job, which is unique at any given time; if a job is deleted, and recreated with the same name, this value will remain unchanged. However, the Guid value will be different. X X
rundate The date/time of the execution start time of the job (normally the same as the executionId) X X
sourceexecutionid The Unix timestamp in milliseconds of the execution start time of the change log being applied, or replayed. During a normal operation of a Job Reader, this value will be the same as the Job Reader's executionid value. In other words, the value of the Job Reader's Data Pipeline executionid value is the same as the Job Writer's Data Pipeline's sourceexecutionid. X
sourceobject The name of the source object, if known, such as the HTTP source URI, the file name, or the source queue name. Normally blank for database sources, unless when choosing a CDC table as the source. X
sourceobjectname The name of the source object, if known, such as the HTTP source URI X
startindex 1-base index value of next batch of items to be processed on the target (total writes + 1). X
status The overall outcome of the job upon execution (success, failure). Only available during the Finalization Script Execution option. X
targetobject The name of the target object, such as the database table name or the HTTP target URI X
totalwrite The total number of write operations performed so far. As change logs are being applied, larger change logs may contain multiple inner, smaller batches of changes; each completed inner batch increases this value. However, this value remains the same until the next inner batch has completed; if, for example, an inner batch contains 1000 records, but the Max Batch Count is 100, this inner batch will be further processed 10 times; only after the completion of this inner batch will this value be increased by 1000, assuming all records have been pushed successfully. X
upsertcolumns The list of key columns that identifies a unique record X
Paging Markers
highwatermark When paging is applied on the source system (SQL, HTTP High Watermark), represents the current high watermark value X
highwatermarknull When paging is applied on the source system (SQL, HTTP High Watermark), represents the current high watermark value or injects the 'NULL' string if not set X
pagingindex When paging is applied on the source system (HTTP High Watermark) with a simple paging strategy, represents the paging index (1-based) X
pagingmarker When paging is applied on the source system (HTTP High Watermark), represents the paging marker being used X
recordcount When paging is applied on the source system (HTTP High Watermark) with a simple offset strategy, represents the current page count so far (ex: 0, 100, 200...) X
recordindex When paging is applied on the source system (HTTP High Watermark) with a simple offset strategy, represents the current page count+1 so far (ex: 1, 101, 201...) X