Getting Started
Basic Concepts
Tutorials
Advanced Concepts
How-to Guides
Building integration flows
Data transformation
Integration patterns
Developing Components
Tenant Management
Components
CRM Components
ERP Components
E-Commerce Components
Basic Components
Utility Components
References
Sailor
Component Descriptor
Covered in this article
Related articles
Contracts and Workspaces Understanding credentials Data Sample Overview Creating your first integration flow Getting Started What is an integration component? What is an integration flow? What is a tenant? Take a tour of the product Creating a webhook flow Building a component in Java Building a component in Node.js Content-Based Routing Deploying a component How-to Guides Managing flow history Managing OAuth Clients Managing user roles in a tenant Managing Workspaces Mapping data Building real-time flows Sending data to a list of recipients REST API component Managing your SSH keys Managing your Teams/Components Allmysms component Amazon MWS component AMQP component AppDirect Component AWS Lambda component AWS SNS component Bazaarvoice component ChannelAdvisor component Code component Configuration component CSV component Docusign component Dun-and-bradstreet component Ebay component Edifact-parser component Email component Exact Online component Filter component Google PubSub component Google-shopping component Google-translate component Goto-webinar component Google Spreadsheets component Components JDBC Component JDBC Component JDBC Component Jsonata component Key-value-component component Ldap component Lightspeed-ecom component Lightspeed-retail component Lionbridge-translation component Magento1 component Magento2 component Mailchimp component Mandrillapp component Mapper component Microsoft Dynamics CRM component NetSuite component ODATA component Outlook component Petstore-nodejs component Pipedrive component Postgresql component Quickbooks-component component Request-reply component Rest API component Router component Salesforce-cpq component Salesfoce Component Sdl-translate component Sftp component Shopify-admin component Shopware component Simple-trigger component Smarty-streets component Soap component Splitter component Stripe component Sugar-crm component Timer component Twitter component Webhook component Xml component Component Descriptor Structure References Sailor compatibility matrix View Classes

MsSQL component


Description

elastic.io integration component for Microsoft SQL Server

Authentication

You may use following properties to configure a connection:

image

other types of configuration parameters are also supported, more information 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.

image

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

image

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 injection, 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 incoming metadata - as soon as your query is in place it will be parsed and incoming metadata will be generated accordingly.

Known issues

No known issues are there yet.

License

Apache-2.0 © elastic.io GmbH