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.
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.
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.
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:
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.
|
This page will automatically be redirected to the sign-in page in 10 seconds.
Hello.
Thanks for your helpful article. I have the same problem, I want to send PUT request after insert into a table. So, I want to use a trigger for this purpose. But it seems there is a problem with this trigger definition. My transaction never executes! Because when it comes to "EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer" does not continue anymore. What should I do? My platform is windows 10 and SQL Server 2014.
Regards.
Hi Mohammad Shojaeinia,
Please specify "OUTPUT" type for @vPointer parameter while creating OLE object instance. EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT
We have also updated the documentation.
Regards, Abinaya Karuppasamy
Hello again,
thanks for your great article and your fast reply to my last comment. Another problem I have is that I want to dynamically create the URL. For instance, I want to have: http://someurl.com/api/v1/{id}/feed and I should get the id from the query: (select ID from inserted).
How can I do this?
Regards
Hi Mohammad,
Please follow the below steps to dynamically generate the URL based id from the SQL select query.
Regards, Abinaya.