How to extract data from database, transform, and load into web service

Create an ARRAY of rows from all records in a table and send it as a payload in a single HTTP call

In this example, we create a Flow that reads all the records from a database table, creates a JSON ARRAY that includes all the records, and sends it as a payload to the API endpoint in a single HTTP call.

In Connections

Access Connections here.

Step 1. Create a source database Connection.

Step 2. Create a destination HTTP Connection.

Step 3. Create a JSON Format with all the default settings. 

In Flows

Access Flows here.

Step 4. Create a Database to Web Service  Flow.

Step 5. Add a new source-to-destination transformation. Select FROM Connection created in step 1 and TO Connection and Format created in steps 2 and 3. Select the source table name and the destination endpoint. Optionally, you can modify the Source SQL under MAPPING.

Step 6. Modify the per-field Mapping if needed. 

Step 7. Test the transformation.

Assuming that the following fields are in the source table (or fields returned by the Source Query): id, first_name, last_name, dob the output from the test transformation will look like this:

[{"id":1, "first_name":"Joe", "last_name":"Doe", "dob":"01/01/2000",
....
{"id":2, "first_name":"Jane", "last_name":"Doe", "dob":"01/01/2001"]

If you are happy with the output and the payload has the expected structure, then you are done and you can save the Flow. 

Otherwise, use one of the available transformations to modify the payload. Repeat step 7 until it's done.

If the payload has a complicated nested structure, you might consider using JavaScript to create a nested JSON or XML document.

Create a payload from each record in a table and send it to the HTTP endpoint, one record at a time 

In this example, we create a Flow that reads records from a database table (actually SELECT  statement), modifies the custom payload by replacing {tokens} with the values read from the database, and sends the HTTP request using a new payload, one record at a time.  

Step 1. Create a database Connection.

Step 2. Create an HTTP Connection.

In Flows

Access Flows here.

Step 3. Create a Flow Call HTTP endpoint.

Step 4. Use the Connection created in step 2 as a Connection for the Flow created in step 3.

Step 5. Select Parameters tab and enter the payload using {tokens}, where the tokens are field names in a source table. 

Assuming that the following fields are in the source table: id, first_name, last_name, dob and the payload looks like the following:

{
   "source": "test",
   "userId": {id}, 
   "firstName": {first_name},
   "lastName": {last_name},
   "dateOfBirth": {dob}
}

The output after merging the data with the template will look like this:

{
   "source": "test",
   "userId": 1, 
   "firstName": "Joe",
   "lastName": "Doe",
   "dateOfBirth": "01/01/2000"
}

Step 6. Create a nested flow.

Step 7. Add Flow created in steps 3 to 5 to the nested flow and click edit condition.

Step 8. Set Loop Type to SQL, Connection to the Connection created in step 1 and enter the driving SQL in the Loop Script field.

The field names in the SELECT statement are case-sensitive.

Modify URL parameters using data in each record in a table and make an HTTP call, one record at a time 

In this example, we create a Flow that reads records from a database table (actually SELECT statement), modifies the URL with parameters for the HTTP endpoint by replacing {tokens} with the values read from the database, and sends the HTTP request using a new URL, one record at a time.  

Step 1. Create a database Connection.

Step 2. Create an HTTP Connection.

When creating a Connection, use {tokens} as a part of the URL to customize URL parameters.

Assuming that the following fields are in the source table: id, first_name, last_name, dob and the tokenized URL looks like the following:

https://myapi.com/?id={id}&firstName={first_name}&lastName={lastName}&dob={dob}

The updated URL for each record in a table will look like this:

https://myapi.com/?id=1&firstName=Joe&lastName=Doe&dob=01/01/2000

In Flows

Access Flows here.

Step 3. Create a Flow Call HTTP endpoint.

Step 4. Use the Connection created in step 2 as a Connection for the Flow created in step 3.

Step 5. Create a nested flow.

Step 6. Add Flow created in steps 3 to 5 to the nested flow and click edit condition.

Step 7. Set Loop Type to SQL, Connection to the Connection created in step 1 and enter the driving SQL in the Loop Script field.

The field names in the SELECT statement are case-sensitive.