Execute CRUD Stored Procedure calls using Syncfusion Data Integration Platform.

Introduction

This ar­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ticle explains how to execute the stored procedure calls with crude operation statements in the SQL database using the Syncfusion Data Integration.

Objective

The ExecuteProcess processor is used in the Syncfusion Data Integration Platform for executing the stored procedure calls.   

Example Dataflow for Executing Stored Procedure Call using ExecuteProcess Processor

Overview

The following table is used in this sample:

Source table name: Tips

Update the size column mentioned in the following table and store the updated columns into new table.

Destination table name: UpdatedTips

List of processors used in this sample

ExecuteProcess Processor

This processor will execute the Stored Procedure query using the SQL cmd utility and its resultant data will be transferred in to flowfile.

Command: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

Command Arguments: -S localhost -U labuser -P coolcomp@123 -d testSample -i E:\DIP_New\Knowledge_Base\Stored_procedure\Execute_Procedure.sql -s "&"

Refer below link for more details

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

ReplaceText Processor

This processor will process the generated flowfile and replace the invalid lines in the incoming flowfile content (including column header and special characters) using the regex expression \([^*]+[-]{3,}.

SplitText Processor

This processor will process the flowfile and split each line of its content into separate flowfile.

ExtractText Processor

This processor will check regular expression against the flowfile content and if it is satisfied, then it will assign the content into the flowfile attribute.

RouteOnAttribute Processor  

This processor checks each flowfile attribute of the valid content.

It will transfer the success flowfiles, if the given property with following expression matches with its incoming flowfile attribute.

validrow: ${input.row.1:getDelimitedField(1):contains('rows affected'):equals(false)}

ReplaceText Processor             

This processor will replace the content of the flowfile using user defined SQL command.

SQL Query:

INSERT INTO [testSample].[dbo].[updatedTips] VALUES (${input.row.1:getDelimitedField(1,'&')},'${input.row.1:trim():getDelimitedField(2,'&')}','${input.row.1:trim():getDelimitedField(3,'&')}','${input.row.1:trim():getDelimitedField(4,'&')}','${input.row.1:trim():getDelimitedField(5,'&')}',${input.row.1:trim():getDelimitedField(6,'&')})

PutSql Processor

This processor will execute the command from flowfile content and insert data into another table.

Destination table: UpdatedTips

Limitations of Executing the stored procedure call in ExecuteProcess Processor

• To perform the crude operation using stored procedure call, both the SQL Server and Syncfusion Data Integration platform should be installed in the same machine.