Configure ETL in DTS migration or synchronization tasks

Modifying ETL configuration on an existing synchronization task includes:

• If you have an existing sync task that does not have ETL configured—that is, when you created the sync task, Configure ETL was set to No—you can change No to Yes and configure a DSL script.

• If ETL was already configured, you can modify the existing DSL script or set Configure ETL to No.

  Important

  • To modify an existing DSL script, first move the synchronization object from Selected Objects back to Source Objects, then re-add it to Selected Objects before editing the DSL script.

  • Migration tasks do not support modifying DSL scripts.

Notes

• Modifying ETL configuration on an existing synchronization task does not support changing the table schema in the destination database. To change the schema, do so in the destination database before starting the synchronization task.

• Modifying ETL configuration may interrupt the data link. Proceed with caution.

• ETL configuration changes apply only to incremental data processed after the task is restarted. They do not affect historical data processed before the change.

• DSL scripts handle data transformation and cleaning operations only. They do not support creating database objects.

• Fields referenced in a DSL script must exist in the source database and must not be filtered out by any filter condition. Otherwise, the task may fail.

• DSL scripts are case-sensitive. Database names, table names, and field names must exactly match those in the source database.

• DSL scripts do not support multiple expressions. Use the e_compose function to combine multiple expressions into one.

• All DML changes from all tables in the source database must produce identical column information after DSL processing. Otherwise, the task may fail. For example, if you use the e_set function to add a column, ensure that INSERT, UPDATE, and DELETE operations from the source database all result in the same additional column in the destination table. For more information, see Record data modification time.

Data processing DSL is a scripting language designed by DTS specifically for data processing in data synchronization scenarios. It supports conditional functions and handles strings, dates, and numeric values, letting you flexibly define data processing logic with the following features:

• Powerful functionality: Offers many functions and supports function composition.

• Relatively simple syntax: Includes examples for common scenarios such as data filtering, transformation, and masking. For details, see Typical scenario examples.

• High execution efficiency: Uses code generation technology to minimize performance impact on the synchronization process.

Constants and variables

• Constants

  Type	Example
  int	123
  float	123.4
  string	"hello1_world"
  boolean	true or false
  datetime	DATETIME('2021-01-01 10:10:01')

• Variables

  Variable	Description	Data type	Example value
  __TB__	Table name	string	table
  __DB__	Database name	string	mydb
  __OPERATION__	Operation type	string	__OP_INSERT__, __OP_UPDATE__, __OP_DELETE__
  __BEFORE__	Pre-image value for UPDATE operations (value before change) Note DELETE operations have only pre-image values.	Special marker, no type	v(`column_name`,__BEFORE__)
  __AFTER__	Post-image value for UPDATE operations (value after change) Note INSERT operations have only post-image values.	Special marker, no type	v(`column_name`,__AFTER__)
  __COMMIT_TIMESTAMP__	Transaction commit time	datetime	'2021-01-01 10:10:01'
  `column`	Value of the specified column for a record	string	`id`, `name`

Expression functions

• Numeric operations

  Function	Syntax	Value range	Return value	Example
  Addition	op_sum(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_sum(`col1`, 1.0)
  Subtraction	op_sub(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_sub(`col1`, 1.0)
  Multiplication	op_mul(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_mul(`col1`, 1.0)
  Division	op_div_true(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_div_true(`col1`, 2.0); if col1=15, returns 7.5.
  Modulo operation	op_mod(value1, value2)	value1: integer or floating-point number value2: integer or floating-point number	Returns an integer if both parameters are integers; otherwise, returns a floating-point number.	op_mod(`col1`, 10); if col1=23, returns 3.

• Logical operations

  Function	Syntax	Valid values	Return value	Example
  Equality check	op_eq(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	Boolean: true or false	op_eq(`col1`, 23)
  Greater-than check	op_gt(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	Boolean: true or false	op_gt(`col1`, 1.0)
  is less than	op_lt(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	Boolean: true or false	op_lt(`col1`, 1.0)
  Greater-than-or-equal check	op_ge(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	Boolean: true or false	op_ge(`col1`, 1.0)
  Less-than-or-equal check	op_le(value1, value2)	value1: integer, floating-point number, or string value2: integer, floating-point number, or string	Boolean: true or false	op_le(`col1`, 1.0)
  AND operation	op_and(value1, value2)	value1: boolean value2: boolean	Boolean: true or false	op_and(`is_male`, `is_student`)
  OR operation	op_or(value1, value2)	value1: boolean value2: boolean	Boolean: true or false	op_or(`is_male`, `is_student`)
  IN operation	op_in(value, json_array)	value: any type json_array: JSON-formatted string	Boolean: true or false	op_in(`id`,json_array('["0","1","2","3","4","5","6","7","8"]'))
  Check if value is null	op_is_null(value)	value: any type	Boolean: true or false	op_is_null(`name`)
  Check if value is not null	op_is_not_null(value)	value: any type	Boolean: true or false	op_is_not_null(`name`)

• String functions

  Function	Syntax	Value range	Return value	Example
  String concatenation	op_add(str_1,str_2,...,str_n)	str_1: string str_2: string ... str_n: string	Concatenated string	op_add(`col`,'hangzhou','dts')
  String formatting and concatenation	str_format(format, value1, value2, value3, ...)	format: string with braces as placeholders, such as "part1: {}, part2: {}" value1: any type value2: any type	Formatted string	str_format("part1: {}, part2: {}", `col1`, `col2`); if col1="ab" and col2="12", returns "part1: ab, part2: 12".
  String replacement	str_replace(original, oldStr, newStr, count)	original: original string oldStr: string to replace newStr: replacement string count: integer, maximum number of replacements. Set to -1 for all occurrences.	Replaced string	str_replace(`name`, "a", 'b', 1); if name="aba", returns "bba". str_replace(`name`, "a", 'b', -1); if name="aba", returns "bbb".
  Replace values in all string-type fields (such as varchar, text, char)	tail_replace_string_field(search, replace, all)	search: string to replace replace: replacement string all: whether to replace all matches; currently supports only true. Note If you do not want to replace all matches, use the str_replace function.	Replaced string	tail_replace_string_field('\u000f', '', true) replaces all occurrences of "\u000f" in string-type field values with a space.
  Remove specified characters from start and end of string	str_strip(string_val, charSet)	string_val: original string char_set: set of characters to remove	String with leading and trailing characters removed	str_strip(`name`, 'ab'); if name=axbzb, returns xbz.
  Convert string to lowercase	str_lower(value)	value: string column or string literal	Lowercase string	str_lower(`str_col`)
  Convert string to uppercase	str_upper(value)	value: string column or string literal	Uppercase string	str_upper(`str_col`)
  Convert string to number	cast_string_to_long(value)	value: string	Integer	cast_string_to_long(`col`)
  Convert number to string	cast_long_to_string(value)	value: integer	String	cast_long_to_string(`col`)
  Count substring occurrences	str_count(str,pattern)	str: string column or string literal pattern: substring to find	Number of times the substring appears	str_count(`str_col`, 'abc'); if str_col="zabcyabcz", returns 2.
  Find substring position	str_find(str, pattern)	str: string column or string literal pattern: substring to find	Position of first match; returns `-1` if not found	str_find(`str_col`, 'abc'); if `str_col="xabcy"`, returns `1`.
  Check if string consists only of letters	str_isalpha(str)	str: string column or string literal	true or false	str_isalpha(`str_col`)
  Check if string consists only of digits	str_isdigit(str)	str: string column or string literal	true or false	str_isdigit(`str_col`)
  Regular expression matching	regex_match(str,regex)	str: string column or string literal regex: regular expression string column or string literal	true or false	regex_match(__TB__,'user_\\d+')
  Mask part of a string with a specified character for data masking, such as replacing the last four digits of a phone number with asterisks	str_mask(str, start, end, maskStr)	str: string column or string literal start: integer, starting position for masking (minimum 0) end: integer, ending position for masking (maximum string length minus one) maskStr: single-character string, such as '#'	String with characters from start to end masked	str_mask(`phone`, 7, 10, '#')
  Extract part of string after cond	substring_after(str, cond)	str: original string cond: string	String Note Result does not include cond.	substring_after(`col`, 'abc')
  Extract part of string before cond	substring_before(str, cond)	str: original string cond: string	String Note Result does not include cond.	substring_before(`col`, 'efg')
  Extract part of string between cond1 and cond2	substring_between(str, cond1, cond2)	str: original string cond1: string cond2: string	String Note Result does not include cond1 or cond2.	substring_between(`col`, 'abc','efg')
  Check if value is a string type	is_string_value(value)	value: string or column name	Boolean: true or false	is_string_value(`col1`)
  Replace content in string-type fields; starts from the end in reverse order	tail_replace_string_field(search, replace, all)	search: string to replace replace: replacement string all: whether to replace all; true or false	Replaced string	Replace "\u000f" with a space in all string field values. tail_replace_string_field('\u000f','',true)
  Get value of a field in MongoDB	bson_value("field1","field2","field3",...)	field1: top-level field name field2: second-level field name	Value of the specified field in the document	e_set(`user_id`, bson_value("id")) e_set(`user_name`, bson_value("person","name"))

Global functions

• Flow control functions

  Function	Syntax	Parameters	Example
  if statement	e_if(bool_expr, func_invoke)	bool_expr: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke: function call. Supported: e_drop, e_keep, e_set, e_if, e_compose	e_if(op_gt(`id`, 10), e_drop()); drops the record if ID > 10.
  if-else statement	e_if_else(bool_expr, func_invoke1, func_invoke2)	bool_expr: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke1: function call executed if condition is true. func_invoke2: function call executed if condition is false.	e_if_else(op_gt(`id`, 10), e_set(`tag`, 'large'), e_set(`tag`, 'small')); sets tag to "large" if ID > 10, otherwise to "small".
  Switch-like statement that evaluates multiple conditions and executes the first matching operation. Executes a default operation if no conditions match.	s_switch(condition1, func1, condition2, func2, ..., default=default_func)	condition1: boolean constant or function call. Constants: true or false. Function call example: op_gt(`id`, 10). func_invoke: function call. Checks condition1; if true, executes this function and exits the switch. If false, checks the next condition. default_func: function call executed if all conditions are false.	e_switch(op_gt(`id`, 100), e_set(`str_col`, '>100'), op_gt(`id`, 90), e_set(`str_col`, '>90'), default=e_set(`str_col`, '<=90')).
  Combine multiple operations	e_compose(func1, func2, func3, ...)	func1: function call. Can be e_set, e_drop, e_if. func2: function call. Can be e_set, e_drop, e_if.	e_compose(e_set(`str_col`, 'test'), e_set(`dt_col`, dt_now())); sets str_col to "test" and dt_col to the current time.

• Data manipulation functions

  Function	Syntax	Parameters	Example
  Drop this record (do not synchronize)	e_drop()	None	e_if(op_gt(`id`, 10), e_drop()); drops records where ID > 10.
  Keep this record (synchronize to destination)	e_keep(condition)	condition: boolean expression	e_keep(op_gt(id, 1)); synchronizes only records where ID > 1.
  Set column value	e_set(`col`, val, NEW)	col: column name val: constant or function call. Must match col's data type. NEW: converts col to val's data type (optional) Important If you omit NEW, do not include the preceding comma. Ensure data type compatibility to avoid task errors.	e_set(`dt_col`, dt_now()); sets dt_col to current time. e_set(`col1`, `col2` + 1); sets col1 to col2 + 1. e_set(`col1`, 1, NEW); converts col1 to numeric type and sets it to 1.
  MongoDB field retention, field dropping, and field name mapping	e_expand_bson_value('*', 'fieldA',{"fieldB":"fieldC"})	*: field names to retain; * means all fields. fieldA: field names to drop. {"fieldB":"fieldC"}: field name mapping; fieldB is the source field name, fieldC is the destination field name. Note Field name mapping is optional.	e_expand_bson_value("*", "_id,name"); writes all fields except _id and name to the destination.