Expression Editor and New Data Integration Engine for Import

August 27, 2020

We keep developing our new data integration engine, and now it is finally available for Import packages. It finally allows agent connections in import and brings back the expression editor along with a completely new expression syntax.

The new engine for import is yet in beta status and is not used by default. As well as for export and replication, you need to explicitly enable it in your packages, by selecting the Use New Runtime check box. Please note that the new engine uses a completely different expression syntax.

Use New Runtime check box in Import Package

However, if you want to switch an existing package that uses expression mapping to a new runtime, you will need to review the expressions used and rewrite them to the new syntax.

New Expression Syntax

The new data integration engine uses a completely new syntax. You can find its description in our documentation, and here we just describe the main differences between the new and old syntax that you need to consider.

Quoting

One of the main differences is quoting of column names and string constants. In old engine, column names that required quoting, are quoted with square brackets. In our new syntax they are quoted with double quotation marks.

String constants (literals), on the other hand, are quoted with double quotation marks in old engine. In Skyvia's new expression syntax, they are quoted with single quotation marks.

So the expression, uniting the First Name and Last Name fields and inserting a space between them, looks like the following for the old runtime:

[First Name]+" "+[Last Name]

and here is how it should look for the new runtime:

"First Name" + ' ' + "Last Name"

Data Types and Type Conversion

Another important difference is that the new expression engine uses different data types and type conversion syntax. In the old runtime, you need to type the required type name and additional parameters, separated with commas. In the new expression syntax, you simply use type conversion functions and pass a value to convert to them without any additional parameters. So, an expression, converting, for example, a numeric value to a string, looks like the following for the old runtime:

(DT_WSTR,38)accountid

In the new expression syntax, it looks simply like this:

string(accountid)

You can find the complete list of type conversion functions in our documentation.

Functions

The new expression engine provides all the functions, available in old engine, and some additional functions. The names of functions are also very similar, however, there are the following differences:

  • Function names are lowercase, and they are case-sensitive. You cannot just take expressions with uppercase functions from your old expressions and use them as is - you need to convert them to lowercase.

  • When a function name consists of two words, an underscore character is added between these words in Skyvia's new expression syntax. For the old engine, no character is added. For example, the old REPLACENULL function corresponds to the replace_null function in new expression syntax.

Corresponding functions mostly have the same argument lists in both old and new Skyvia's runtimes.

However, you don't need to worry much about studying all these differences, because together with the new expression syntax, Skyvia brings back a convenient expression editor for the new runtime.

Expression Editor

Expression editor helps you to quickly enter your expressions and make less mistakes. Instead of typing them into a small box when editing mapping, you can conveniently edit them in a multiline editor with a number of useful features:

  • Expression editor lists all the available columns and functions, and you can just click them to add to your expressions. No more manual typing errors!

  • Expression editor offers syntax highlighting for your expressions, making editing them more convenient.

  • Code completion allows you to type in functions and column names faster; it also shows the argument list for a function and the list of overloads, if any.

Expression editor

Agent Connection Support

With the new data integration engine, you get support for agent connections in your import packages. Agent connections greatly simplify connecting to on-premise databases in local networks, using a secure locally installable Agent application as a tunnel for loading data. Now you can easily import data to your local databases with Skyvia or import their data to other sources.

Agent Connection in Import Package

As always, you are welcome to try our new features and leave feedback! We appreciate your support and help in improvements of our products.

Comments