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.

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.

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 OUTPUT

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