Main Differences between Old and New Runtime Syntax
Please consider these expression syntax differences when switching an existing package that uses expression mapping between old and new runtime.
One of the main differences is quoting of identifiers (column names) and string constants. In SSIS syntax, column names that required quoting are quoted by square brackets. In Skyvia’s new expression syntax, identifiers are quoted with double quotation marks.
String constants (literals), on the other hand, are quoted with double quotation marks in SSIS. In Skyvia’s new expression syntax, they are quoted with single quotation marks.
So, for example, an expression, uniting the First Name and Last Name fields and a space between them looks like the following in SSIS syntax, used by the old runtime:
1 [First Name]+" "+[Last Name]
Here is how to write it in the new syntax:
1 "First Name" + ' ' + "Last Name"
In order to use a single quotation mark in a string literal, use two of them.
Data Types and Type Conversion
Old and new Skyvia runtime uses completely different types. New data types are listed in the Data Types and Type Conversion topic.
In SSIS syntax, you need to specify the required type name and additional parameters, separated with commas, enclosed in brackets, before the value to convert. In the new expression syntax, you simply use type conversion functions, and pass a value to convert to the function without any additional parameters. So, an expression, converting, for example, a numeric value to a string, looked like the following in SSIS syntax:
In the new expression syntax, it looks simply like this:
The new expression engine provides all the functions, available in SSIS, and some additional functions. The names of functions are also very similar to SSIS functions, however, there are the following differences:
- Function names are lowercase, and they are case-sensitive. You cannot just take expressions with uppercase functions from SSIS expressions and use them as is, you need to convert them to lowercase.
- When function name consists of two words, in Skyvia’s new expression syntax, an underscore character is added between these words. In SSIS, no character is added. For example, SSIS REPLACENULL function corresponds to the replace_null function in new expression syntax.
Corresponding functions mostly have the same argument lists in both SSIS and new Skyvia’s runtime.
Typed Null Values
If you want to set a column to a null value using expression mapping, you need to provide a null value of the corresponding type. In SSIS expressions you use a NULL function with the data type specified, and with some additional arguments for some data types:
1 2 NULL(DT_STR,10,1252) NULL(DT_DATE)
In our new expression syntax, you use null function with the corresponding type conversion function as an argument, and the latter is called without parameters:
1 2 null(string()) null(datetime())