KingswaySoft JSON Destination component is an SSIS destination component that offers many advanced capabilities in processing incoming data from upstream pipeline components by sending to a service endpoint for writing or processing. The component can work with incoming rows individually or in a batched fashion. When doing so, the component generates JSON document accordingly and write the document to a local file or send it to an HTTP service endpoint to perform a RESTful service call. When interacting with REST API, most services return additional information in the API response which can be advantageous for future integration. For example, when using REST API to send a POST call to create a new record, usually the target application automatically generates a unique id value for the record and return it in the API response, such response often contains the newly created record ID in the target system, which can be very valuable or useful if such information is extracted and properly saved. Such information is very useful if it can be associated back to the original input row. When the service endpoint takes one request at a time, this is usually very straightforward. However, when we work with a service endpoint that takes more than one input row, the response will also contain multiple results, there will have to be a proper way to associate each result object back to the original input in order to make it useful.
In this blog post, we will discuss how you may configure KingswaySoft JSON Destination component to properly handle such API response after the service call is made. We will discuss a few different scenarios how different service respond might look like and we use different strategy accordingly. The ultimate goal is to have the returned result associated back to the original source input, in order for the response data to be relevant and useful, as we have just mentioned.
Example #1 - Single Record Request
The first example works with an endpoint that creates a contact record. Here’s a sample request:
{
"contact": {
"firstname": "John",
"lastname": "Smith",
"email": "[email protected]"
}
}
To send out the above request, we might have a JSON Destination component configure like the following:
The Request Body uses an internal token @[MergedJson] which is the merged JSON document generated by the component based on what has been specified in the Document Designer page. The Document Designer could have been configured as the screenshot below in order to generate the request body.
Note as this REST endpoint works with one record at a time, the Merge Mode would be set to “Every Row” in JSON Destination > Document Designer > Additional Settings. “Every Row” merge mode acts like a batch size of 1. A single file (the JSON document) will be created using the merged JSON from each input row, which is submitted to the service endpoint as defined.
With such a request message, most likely the target application would return the result back as a single object with the newly created record ID included in the response, it can be something like the following:
{
"contact": {
"id": 101,
"createdat": "2021-03-01T17:14:45.567Z",
"firstname": "John",
"lastname": "Smith",
"email": "[email protected]"
}
}
Our goal is to extract the newly generated id value from the API response, and associate the result back to original input row. To do so, we can add a custom output column in the JSON Destination component's Output Columns page which extract the ID from the response using a proper JPath (Relative Path) like something below.
The screenshot above shows the Output Columns configuration to get the newly saved contact id. In this configuration, we have added a new integer-type output column named “Id” with a Relative Path entered as “.contact.id” which can extract the id value from the API response. Let’s set a dummy destination and enable Data Viewer the check the results, we used Data Spawner to generate the random test data.
As you can see, after the execution, the newly created id value is returned along with each input row.
Example #2 - Batch Request with Sequential Response
Next, we will be looking at a service endpoint that supports batch REST API requests, which is more challenging to work with the output. Batch request allows you to make fewer requests with more data in one service call, you can work with multiple records in one request instead of working with one record at a time in each request. Such strategy can help improve integration performance substantially. Having said that, if an API provides a batch enabled service endpoint, we generally recommend you use such service endpoint for better writing performance. The sample JSON below shows a typical batch request, the REST request payload consists of a “contacts” array of objects, each object in the “contacts” array represents an individual contact record.
{
"contacts": [
{
"firstname": "John",
"lastname": "Smith",
"email": "[email protected]"
},
{
"firstname": "Joe",
"lastname": "Doe",
"email": "[email protected]"
}
]
}
As shown in the screenshot below, the JSON Destination component is configured to generate the sample JSON structure.
We use “Every Batch” Merge Mode with a batch size of 200 in JSON Destination for this batch request, which means each service call request will contain the merged JSON document constructed from up to 200 rows in each batch.
The REST batch API response can be something like the JSON document below, it returns a “results” array of objects, each result object is the saved record with the newly created record id included.
{
"results": [
{
"id": 51,
"firstname": "John",
"lastname": "Smith",
"email": "[email protected]"
},
{
"id": 52,
"firstname": "Joe",
"lastname": "Doe",
"email": "[email protected]"
}
]
}
Note that the result is in exactly the same order as the source data. To work with a batch response like this, the JSON Destination component can be configured like something below:
Note that in order to get the newly created contact id and match back with the input rows, we need to enable “Response Is Array” option in JSON Destination Output Columns page. It is important to note that we have the "Response Item Lookup Strategy" configured as Sequential since the result data returned by the service is in the exactly same order as the input, which is what we have discussed previously. In order to extract the Id value from the result, we have specifies the corresponding Response Collection Base Path option set to “.results”, which is the JPath value of the “results” array. We also added an additional output Id column with a relative path of “.id”, which is the path within each response item. With a configuration like this, the JSON Destination component will extract data from “.results[N].id” JPath and output the values in the Id output column. When executing the task, we can see the following result.
As you can observe from the above screenshot, we can see that the JSON Destination component is working according to our expectation and extracted the newly created id values from the batch API response. It is important to note again, using the batch-enabled service endpoint gives us a much better processing or writing performance.
Example #3 - Batch Request with Different Item Order Response
When sending batch REST request, most of the applications process the input the requests in the same sequence order as the input records. But some REST services process the batch in a most efficient order, so that the response items sequence may not follow the exactly same order as the input rows. Microsoft Graph API is one of such APIs when processing batch-based requests.
In such situations, the batch request will require an id property provided for each row, and such id property is primarily used as a correlation value to associate individual responses back to the original requests. Here’s a sample request:
{
"requests": [
{
"id": "1",
"method": "POST",
"url": "/contacts",
"body": {
"firstname": "John",
"lastname": "Smith",
"email": "[email protected]"
}
},
{
"id": "2",
"method": "POST",
"url": "/contacts",
"body": {
"firstname": "Joe",
"lastname": "Doe",
"email": "[email protected]"
}
},
{
"id": "3",
"method": "POST",
"url": "/contacts",
"body": {
"firstname": "Will",
"lastname": "Von",
"email": "[email protected]"
}
}
]
}
If it happens that your source data does not have such id property available for each row, you can generate such id on the fly within the data flow. To do so, you can simply use a Derived Column function Rowindex() which is offered in our Premium Derived Column component, which can be added to your data flow before the JSON destination component. The following screenshot shows how the id value can be generated.
Now let's have a look of the response that we will be receiving, which is something like the following - Note that the response to the batched request has the id value in each response item, which may not follow the exactly the same order as the source data. In some cases, some of the input rows might have errored out, which may not even be in the response.
{
"responses": [
{
"id": "1",
"status": 200,
"body": {
"contactid": 51
}
},
{
"id": "3",
"status": 200,
"body": {
"contactid": 52
}
},
{
"id": "2",
"status": 200,
"body": {
"contactid": 53
}
}
]
}
JSON Destination Output Columns needs to be configured as the screenshot below when working with this sample batch request that the response items can be in a different order. Note that we have changed the "Response Item Lookup Strategy" to "Item Key" this time because the response returned by the service calls is not always in the same order as the source input data.
Similar to the second example, “Response Is Array” option is enabled in JSON Destination Output Columns page and specify the corresponding JPath value “.responses” in the Response Collection Base Path. To match the input rows with output array items, we need to use use “Item Key” Response Item Lookup Strategy. The Item Key matching expression is set to:
- (Expression Mode) [id] == (DT_I4) GetValueFromJson( @[CurrentItem], ".id" )
The Item Key expression matches input id column value with the response id value that is extracted from “.responses[N].id”. GetValueFromJson() function is used to fetch the response id value from the @[CurrentItem] predefined token, @[CurrentItem] represents an item in the array of response items.
Let’s execute our sample package to validate the result. We can see that the JSON Destination Component has correctly matched the response item to the input row as shown below:
Conclusion
By configuring Output Columns in our JSON Destination component, we were able to send JSON formatted REST request to a target server and extract information from the API response. This demonstrates the flexibility of our JSON components when working with different designs of a REST APIs.