mssql-component

elastic.io integration component for Microsoft SQL Server

mssql-component

MSSQL Component component for the elastic.io platform

Environment Variables

No required Environment Variables.

Before you Begin

Before you can deploy any code into elastic.io you must be a registered elastic.io platform user. Please see our home page at http://www.elastic.io to learn how.

Getting Started

Authentication

You may use following properties to configure a connection:

other types of configuration parameters are also supported, more infromation and samples you can find here

SELECT Trigger and Action

With this action you may fetch data out of the database, e.g. using SELECT statement.

This trigger & action has no limitations on the number of rows so you may expect to get all of these via sequential fetching that is implemented within the node.js mssql driver.

Polling

Component will remember last execution timestamp and let you build queries on it:

select * from Leads where Created >= '%%EIO_LAST_POLL%%'

where just before executing the statement the %%EIO_LAST_POLL%% will be replaced with ISO Date of the last execution, for example 2017-08-25T07:43:48.127Z. During the first execution, date will be equal to the bigging of time - 1970-01-01T00:00:00.000Z.

INSERT/DELETE/UPDATE Action

You may use this action to do the operations that are not producing output rows but do the database manipulations, e.g. INSERT, UPDATE or DELETE statements. Internally we use prepared statements, so all incoming data is validated against SQL injetion, however we had to build a connection from JavaScript types to the MSSQL data types therefore when doing a prepared statements you would need to add :type 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)

you should add :type to each @parameter so your SQL query will looks like this:

INSERT INTO 
  Test2.dbo.Tweets 
(Lang, "Text", id, CreatedAt, Username, ScreenName) 
VALUES 
(@lang, @text, @id:bigint, @created_at:date, @username, @screenname)

Following types are supported:

• string (also default type if type is omitted)
• number (will be converted to MSSQL int)
• bigint
• boolean (will be converted to MSSQL bit)
• float
• date (will be converted to DateTime2)
• money

more details can be found here

Component supports dynamic incomig metadata - as soon as your query is in place it will be parsed and incoming metadata will be generated accordingly.

License

Apache-2.0 © elastic.io GmbH