We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.

How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.

 

This document illustrates the steps to start / stop the data integration processors through Restful invocation based on Triggers in SQL Server database.

Goal:

Whenever a new row inserted or existing record modified in a SQL Server table, we need the process the new/modified records in Data Integration Platform (DIP) by fetching them from SQL table. Instead of scheduling the DIP processors on a fixed time to pull records from SQL table, here we are going to use Trigger fired when a record is inserted / modified to invoke DIP REST API to start the processors to pull the new/modified record from SQL Server.

Start / Stop Data Integration Processor:

                Data integration processor can be start or stop using REST full operation. Below are the steps followed to start and to stop the processor.

Step 1 – Construct REST URL to start the processors.

Using ‘Put’ request we can start the data integration processor. Construct the REST URL to be invoked for Starting the processor.

Components

Format

Example

URL

http://<IPAddress>:<PortNo>/dataintegration-api/processors/<ProcessorID>

http://localhost:60017/dataintegration-api/processors/69b51b50-015a-1000-7656-9bf20f2973c7

 

Body

{

                "revision":

                {

                                "version":<Version Number>

                                // Version can be get from GET request

                },

                "component":

                {               

                                "id":<ProcessorID>,

                                "state":"RUNNING"

                }

}

{

                "revision":

                {

                                "version":18

                },

                "component":

                {

                                "id":"69b51b50-015a-1000-7656-9bf20f2973c7",

                                "state":"RUNNING"

                }

}

 

Step 2 – Construct REST URL to stop the processor:

Using ‘PUT’ request we can stop the data integration processor. Construct the REST URL to be invoked for stopping the processor.

Components

Format

Example

URL

http://<Machine IpAddress>:<PortNo>/dataintegration-api/processors/<ProcessorID>

http://localhost:60017/dataintegration-api/processors/69b51b50-015a-1000-7656-9bf20f2973c7

 

Body

{

                "revision":

                {

                                "version":<Version Number>

                                // Version can be get from GET request

                },

                "component":

                {

                                "id":<ProcessorID>,

                                "state":"STOPPED"

                }

}

{"revision":

{

"version":18},

"component":

{"id":"69b51b50-015a-1000-7656-9bf20f2973c7",

"state":"STOPPED"

}

}

 

Step 3 - SQL Trigger query to invoke the processor:

Create SQL Trigger using the following syntax as reference

Query:

Syntax:

CREATE TRIGGER <trigger Name> ON <Table name >

FOR INSERT

AS

declare @vPointer INT;

declare @vResponseText VARCHAR(8000);

declare @vStatus INT;

declare @vStatusText VARCHAR(200);

declare @Parametres nvarchar(3000);

declare @Body as varchar(8000) =

'{

"revision":

{

"version":18

},

"component":

{

"id":"<processorID>",

"state":"RUNNING"

}

} '

SET @Parametres = ''

EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer

EXEC sp_OAMethod @vPointer, 'open',NULL, 'PUT', 'http://<hostname>:60017/dataintegration-api/processors/<processorID>’, 0

EXEC sp_OAMethod @vPointer, 'setRequestHeader', null, 'Content-Type', 'application/json'

Exec sp_OAMethod @vPointer, 'send', null, @Body

EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT

EXEC sp_OADestroy @vPointer

GO

Article ID: Published Date: Last Revised Date: Platform: Control:
7622 03/30/2017 03/30/2017 Big Data Platform General
Did you find this information helpful?
Add Comment
You must log in to leave a comment

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.