Prerequisites

Installed Microsoft SQL database connectors (see tutorial 1. Connector). Completion of the previous tutorial 3. REST API endpoint and successful sending of data to the REST API endpoint

Description

The tutorial below describes how to create an integration task that retrieves data stored on the REST API endpoint and converts it to JSON format.

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 Select data from SQL and generate JSON

1. Create a new Integration Task Step
2. Rename the integration task step: Select data from SQL and generate JSON
3. Change the Data Snapshot to Always, which allows you to browse the data at the output of the integration step
4. In the Connector section, locate SQL and select Microsoft SQL Database Connector from the result.
5. In the Input Scheme section, create a new schema with one parameter:
• ID: integer
6. In the Input Mapping section, map the ID to an ID
7. In the Output Schema section, create a new schema with one parameter:
• JSON: JSON
8. In the Configuration section:
• Load an existing SQL Connection string via the blue list icon
• Paste the following code into your configuration:
  / * - SIMPLE SOLUTION - Return specific record by ID from GET param DECLARE @ID int = (SELECT TOP 1 ID FROM #InputData) SELECT ( SELECT [id] , [fruit] , cast ([price] as decimal (10,2)) as [price] FROM [dbo]. [dx_demo_fruits] WHERE ID = @ID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) AS JSON * / - ADVANCED SOLUTION - Return specific record by ID from GET param or return all records if ID param is not used DECLARE @ID int = (SELECT TOP 1 ID FROM #InputData) DECLARE @SQL nvarchar (max) = ' SELECT ( SELECT [id] , [fruit] , cast ([price] as decimal (10,2)) as [price] FROM [dbo]. [dx_demo_fruits] ' + IIF ( @ID IS NOT NULL , 'WHERE ID =' + cast (@ID as nvarchar (10)) , '' ) + ' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER ) AS JSON ' EXEC (@SQL)
• Save and close

4. Starting the integration task

1. Switch the Runnable / Not runnable checkbox at the top of the screen to the Runable - green backlight state. You may notice that at the same time the History / Design Switch
changes on the left
2. 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
3. Press the green Start button next to the History button. A green message will appear in the lower right corner
4. Touch the Reload task runs button on the left side of the screen
5. Press the orange Snapshot Data button on the last green integration steps and compare the outputs