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 KB article explains the steps to start and stop the data integration processors by invoking the REST API using the Triggers in the SQL server database.
Goal:
Whenever a new row is inserted or an existing record modified in a SQL server table, the trigger gets fired and the REST API will be invoked to start the Data Integration processors. Instead of scheduling the Data Integration Platform processors on a fixed time to pull records from the SQL table, here, we are going to fire the Trigger when a record is inserted or modified to invoke the Data Integration Platform REST API to start the processors to pull the new or modified record from the SQL server.
Start and Stop Data Integration Processor:
Data integration processor can be started or stopped using the REST API. Follow these steps to start and stop the processor.
Step-1: Get the processor version.
Using the ‘GET’ request, we can get the version of the data integration processor in its JSON response.
http://localhost:60017/dataintegration-api/processors/<ProcessorID>
|
The ProcessorID of the GenerateFlowFile processor is highlighted in the following image.
Step-2: Construct a REST API to start the processors.
Using the ‘PUT’ request, we can start the data integration processor. Construct the REST API to be invoked for starting the processor.
Components | Format | Example |
URL | http://<Hostname>:<PortNo>/dataintegration-api/processors/<ProcessorID> | http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f
|
Body | { "revision": { "version":<Version Number> // Version can get from the GET request. }, "component": { "id":<ProcessorID>, "state":"RUNNING" } } | { "revision": { "version":18 }, "component": { "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f", "state":"RUNNING" } } |
Step-3: Construct the REST URL to stop the processors.
Using the ‘PUT’ request, we can stop the data integration processor. Construct the REST API to be invoked for stopping the processor.
Components | Format | Example |
URL | http://<Hostname>:<PortNo>/dataintegration-api/processors/<ProcessorID> | http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f
|
Body | { "revision": { "version":<Version Number> // Version can get from the GET request. }, "component": { "id":<ProcessorID>, "state":"STOPPED" } } | { "revision": { "version":18 }, "component": { "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f", "state":"STOPPED" } } |
Step-4: State of the processor.
The processor is in a stopped state in the Data Integration Platform before the trigger gets fired.
Step-5: SQL Trigger query to invoke the processor:
Create a SQL Trigger for a SQL table using the following syntax as a 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":<version number can get from step-1> }, "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 |
Make sure you have created any SQL table before creating a trigger using the following query as a reference. |
Step-6: Invoking the REST API.
The trigger will get fired and the Data Integration processor will be started running as follows if any records are inserted or modified into the created SQL table.
