Prerequisites

Installed Microsoft SQL database connectors (see tutorial 1.)

Description

In the following tutorial, we will create a new integration task that will process input data in JSON format from the endpoint and store it in an MSSQL database. To test this task, you need to send data to the endpoint. For this purpose, you can use the POSTMAN application or any REST application that allows you to send data using the POST method.

1. Login

Log in to Xeelo DX and click the Tutorials tile in the Companies section.

2. Creating an integration task

Create a new integration task using the Add button. Change the job name and confirm.

3. Creating an integration step Insert data from endpoint into SQL table

1. Create a new task integration step
2. Rename the integration task step: Insert data from endpoint into SQL table
3. In the Connector section, locate SQL and select Microsoft SQL Database Connector from the result.
4. In the Input Scheme section, select the JSON schema via the blue list icon
5. In the mapping section, map the input automatically
6. In the Output Scheme section, select JSON with null schema via the blue list icon
7. In the Configuration section:
• Load an existing SQL Connection string via the blue list icon
• Paste the following code into your configuration:
  DECLARE @JSON NVARCHAR (MAX) = (SELECT TOP 1 JSON FROM #InputData) DECLARE @Inserted INT, @Updated INT - select record from JSON structure into temp table SELECT * INTO #JSONData FROM OPENJSON (@JSON) WITH ( [id] INT '$ .id' , [fruit] NVARCHAR (255) '$ .fruit' , [price] FLOAT '$ .price' ) - join temp table with data in database and find data for update UPDATE [db] SET [fruit] = [input]. [fruit] , [price] = [input]. [price] FROM #JSONData AS [input] INNER JOIN [dbo]. [Dx_demo_fruits] AS [db] ON [input]. [Id] = [db]. [Id] SET @Updated = @@ ROWCOUNT - join temp table with data in database and find new records for insert INSERT INTO [dbo]. [Dx_demo_fruits] ([id], [fruit], [price]) SELECT [input]. [id] , [input]. [fruit] , [input]. [price] FROM #JSONData AS [input] LEFT JOIN [dbo]. [Dx_demo_fruits] AS [db] ON [input]. [Id] = [db]. [Id] WHERE [db]. [id] IS NULL SET @Inserted = @@ ROWCOUNT SELECT ( SELECT @Inserted AS [Inserted] , @ Updated AS [Updated] FOR JSON PATH ) AS [JSON]
• Save and close

4. Creating the Xeelo DX endpoint Give fruit

1. Now we need to create an endpoint where we will send the data. Use the box navigation to exit Tasks and press the Endpoint tile in Tutorials
2. Press Add
at the top left
3. In the General section:
• Name the endpoint Give fruit
• Use the
button to generate a slag
• Enter *
in the IP filter
4. In the POST section of the integration task (POST triggered task):
• Select the Insert data from endpoint into SQL table integration task
• Uncheck Start task async - only one instance of the task will be able to run, the others will wait in the queue
• In the Input section, select JSON and add $ {points}
to the mapping
• In the Output section, select JSON with null, select the output step Insert data from endpoint into SQL table and automatically map to the last step (double arrow)
• Save endpoint
5. Copy the endpoint address at the top of the endpoint, e.g. https://your-dx-tutorial-site.online/api/endpoint/tutorials/default-group/send-me-a-fruit

5. Starting the integration task

1. Return to the integration task
2. Switch the Runnable / Not runnable checkbox at the top of the screen to the Runable - green backlight state. You may notice that the History switch on the left changes at the same time History / Design
3. Refresh the Xeelo DX cache using the gray recycle button at the top right of the screen, next to the profile. This will load the Xeelo DX cache and its steps into the cache for faster integration
4. Open Postman application or any similar application
5. Fill the endpoint, set POST method and send data below:
{ "id" : 49 ,"fruit" : "Mango" ,"price" : 12.20 }