Advanced Job Options

Version 24.3.9110


Advanced Job Options

Version 24.3.9110


Advanced job options provide greater control over your data flows. You can also fine-tune how your data is extracted from your source and how it is replicated to your database by overriding the default values for these options.

These advanced options are available from the Advanced tab in Job Settings.

Replication Options

You can set replication options (in the Replicate Options section on the Advanced tab) at the query level or at the job level. Options that you set at the query level affect that query only, and they take higher precedence than those set at the job level.

The following table describes the available replication options and their default values:

Option Name Default Value Description
Table Name Prefix None Applies a prefix to all your destination tables. This option applies to general REPLICATE commands only. For example, the command Replicate [Accounts], with a prefix of sync_, replicates your Accounts data to the sync_Accounts table in your destination.
Destination Schema None Specifies the schema that should be used in the destination. This option is important when multiple schemas are defined for the database.
Status Connection Default Specifies a database connection to save your job status. By default, the database is stored in the same directory as App settings.
Alter Schema True Specifies whether to alter the job schema. If this option is set, the job adds columns and alters data types in the destination table when there is a mismatch between the source table metadata and the destination table metadata.
Auto-Truncate Strings False Specifies whether the job should truncate strings automatically. If this option is set to True, the job does automatically truncate strings, based on the destination column size.
Batch Size 1000 Controls the number of records to send to the destination table at one time. Higher batch size generally means better overall performance, although a higher time-out value might be needed.
Command Timeout 300 Controls the timeout, in seconds, for destination connections.
Continue On Error True Specifies whether the job should continue to the next query when it encounters an error. If this option is set to True and the job encounters an error, it continues to the next query in the job instead of stopping.
Convert Date-Time Values to GMT False Converts all local time to GMT before inserting into the database.
Drop Table False Specifies whether to drop the existing destination database table. If this option is set to True, the job drops the existing destination database table before it caches the new results. Use this option when you want to refresh the entire destination database table, including its schema.
Replicate Interval 180 Paired with the Replicate Interval Unit option, this option controls the time frames in which to split the data when the process retrieves data.
Replicate Interval Unit days Paired with Replicate Interval, this option controls the time frames in which to split the data when the process retrieves data. Accepted values are minutes, hours, days, weeks, months, and years.
Replicate Start Value None Sets the minimum date/ int value (date in the form yyyy-MM-dd) from which to begin replication.
Deletion Behavior Hard Delete Controls how the job manages deleted data in your source. The Skip Delete value ignores deleted records, Hard Delete removes deleted records from your destination, and Soft Delete leaves records in the destination but marks them as deleted. (Not all sources support this feature.)
Truncate Table Data False Specifies whether to truncate table data. If this option is set to True, the job removes existing rows in the destination database table before adding the selected rows. Use this option when you want to refresh the entire destination database table but keep its existing schema.

Additional Options

You can set several less-typical options in the Additional Options field on the Advanced tab. Use name-value pairs separated by a comma to specify multiple options, as shown in this example:

ColumnNameIncludeVowels=true,TransactionSize=100000

The following table describes these options and their default values:

Option Name Default Description
BytesPerCharacter 1 Specifies how many bytes to allocate in the cache for each character. You can use this option to work around an issue for some destinations where the column size is denoted by byte size and not column size. The column sizes that are reported do not work in cases where more than one byte is allocated per character.
ColumnNameIncludeVowels True Specifies whether the column name includes vowels. When the option is set to False, vowels are removed from column names. This option is useful for shrinking column-name sizes to avoid identifier limits.
ColumnNameMaxSize -1 Determines the maximum size of column names. A value of -1 represents that there is no maximum size, so the job defaults to the maximum column size that is reported by the destination.
MinLastModTimeOffset 300 Specifies the minimum difference in seconds between the LastRunTime and LastModTime columns. This option replaces the ClockSkewInterval advanced option.
MaxLastModTimeOffset 0 Specifies the maximum difference in seconds between the LastRunTime and LastModTime columns. Setting this option to 0 ensures that the replication engine saves the last run time (LastRunTime) as the last-modified time (LastModTime).
ReplaceInvalidDatesWithNull True Specifies whether invalid dates are replaced with a null value. If this option is set to True, all invalid datetimes that are reported by the source table are converted to a null value to avoid replication errors.
ReplicateEndDate None Sets the maximum date to which to replicate (in the form yyyy-MM-dd).
StoresUppercaseIdentifiers False Specifies whether identifiers in the cache are stored with all uppercase characters. If this option is set to True, the identifiers are stored in all uppercase characters.
StoresLowercaseIdentifiers False Specifies whether identifiers in the cache are stored with all lowercase characters. If this option is set to True, the identifiers are stored in all lowercase characters.
Transaction Size 50000 Controls the number of records that are retained in memory before the records are committed to the destination table.
LongTextSize None Sets the maximum column size before a VARCHAR column is converted to varchar(max). For example, setting this option to 255 leaves as is all VARCHAR columns with a column size of less than or equal to 255 and sets all column sizes greater than 255 to varchar(max). This option is useful for truncation issues.