Microsoft SQL Server component
The following properties are available when configuring a connection to your Microsoft SQL Server database:
Other types of configuration properties are also supported, which are described in more detail in the Microsoft SQL Server client for Node.js npm package documentation.
LOG_LEVEL - controls verbosity of logger. Valid values:
Use the Microsoft SQL Server select keyword trigger and action to fetch data from the database, e.g.
When configuring this trigger and action, you can also see the mapping results immediately:
There are no limitations on the number of rows which this trigger and action operates on.
You can expect to retrieve all rows via sequential fetching implemented within the node.js
The Microsoft SQL component remembers the last execution timestamp and lets you build queries on from this information, e.g.
select * from Leads where Created >= '%%EIO_LAST_POLL%%'
where just before executing the statement the
%%EIO_LAST_POLL%% is replaced with the ISO date of the last execution, for example
During the first execution, the date is equal to Epoch time -
Refer to SELECT trigger and action (above).
This component supports the Microsoft SQL Server insert, delete and update keywords for its action:
When configuring this action, you can also see the mapping results immediately:
This action performs operations that do not generate rows of output but instead, performs data manipulations within the database, e.g.
Internally, statements are prepared before being sent to the database, so all incoming data is validated against SQL injection.
:type syntax to each prepared statement variable.
For example, if you have a following SQL statement:
INSERT INTO Test2.dbo.Tweets (Lang, "Text", id, CreatedAt, Username, ScreenName) VALUES (@lang, @text, @id, @created_at, @username, @screenname)
Add the relevant
:type syntax to each
@parameter, so your SQL query would look something like:
INSERT INTO Test2.dbo.Tweets (Lang, "Text", id, CreatedAt, Username, ScreenName) VALUES (@lang, @text, @id:bigint, @created_at:date, @username, @screenname)
The following types (i.e. representing the
:type syntax), are supported:
string(also default type if type is omitted)
number(will be converted to MSSQL
boolean(will be converted to MSSQL
date(will be converted to
The Microsoft SQL Server component supports dynamic incoming metadata. As soon as your query is in place, the query is parsed and incoming metadata is generated accordingly.