There could be various reasons why a retry would be necessary when working with API-based integration. At times, an API-side error, like throttling or Internal server-side errors, would be resolved with simple retries through some purpose-designed back-off intervals. Or, at certain times, based on an error code, certain request parameters might need to be reset and sent using new values that are different from the initial request. This would be purely dependent on the API; however, having these handled in the ETL design process could be crucial to ensure a successful job execution, avoiding any hiccups on the way. Our KingswaySoft HTTP and JSON/XML Components provide some simple and efficient strategies to accomplish such goals with minimum effort. In this blog post, we will demonstrate two ways in which the error code can be handled by having a retry strategy implemented in order to provide a resilient integration process. To begin with, we will be using the following 3 components from our SSIS Productivity Pack product along with one out-of-box SSIS component:
- HTTP Connection Manager
- JSON Destination Component
- Premium Derived Column component
- Conditional Split component
Leveraging the Built-in Automatic Retries Provided by the HTTP Connection Manager
Let's consider this case - A JSON Destination component is used to send a service call to an API endpoint. And the API provides a set of responses from which you wish to know when the request fails on certain error codes. Now a failure is feasible if it is an expected one. However, in some cases, before it's captured as a failure, a certain amount of retries would be essential. Take 503 Service Unavailable, or 429 too many requests, as an example. It wouldn't be desirable for the SSIS package to fail, or say, the batch to fail, as soon as this error occurs. Instead, you could retry the request after a back-off interval to see if it succeeds. In the HTTP Connection Manager, when you navigate to Advanced Settings, the options shown below would help with this. Set the error code by clicking on the "+" button to have it recognized as an error by our components, and you could set it as retriable.
When the package is being executed, as you can see below, and a 503 error is encountered, the sessions get retried by the HTTP Connection Manager.
Now, the retry happens up to 4 times based on different back-off intervals, and then if the error still persists, it fails.
Ad-hoc Retries
Now that we have seen how the HTTP Connection Manager makes retries using a built-in strategy, let's see how we can implement an ad-hoc retry strategy so that we can let the data pass through to a downstream pipeline component, and make a retry there so that the error does not propagate up to fail the entire data flow or the entire ETL process. In doing so, we will show you a method that would send the retry request after making some on-fly modifications, in order to make sure that the retry will work properly. Consider the case where we have the JSON Destination Component sending a batch request to the API endpoint, and if we come across a specific error code, such as 409 - Conflict, the API endpoint we are working with would expect a different query string parameter to be sent along with the request. Here, we would need to,
- first, make sure that the error doesn't stop the execution, and instead, it's handled in the data flow pipeline,
- evaluate and see the error code is 409, and then
- dynamically modify the query string parameter in our JSON Destination component, and retry the request.
In order to do this, as a first step, in the HTTP Connection manager -> Advanced Settings, contrary to what we did before, make sure that the error code 409 is not specified in the Error handling section. And then, when this error occurs from the API side, the JSON Destination will output the error code when we enable the "HTTP Response Code" as shown below.
And then, in the JSON component, navigate to the General page, and parameterize the Query String parameter by using the "fx" button, followed by the "Expression mode". This variable will be used in a conditional expression to be dynamically changed based on the error code we receive.
Once we have this, we can build a conditional expression using the Conditional Split Component by evaluating the HTTP Response code column against the desired error code (409, in our case). Then, based on that, change the query string parameter to a new value/variable using the Premium Derived Column component.
The expression would be something as follows.
WriteValueToVariable( @[User::Variable], "NewValue")
Please note that the conditional expression, if not TRUE, would not run the rest of the data flow, thereby not having a retry in such cases where the error does not occur. Another change, based on your design, would be to have a new variable in the second JSON Destination if you plan to use this in a foreach loop, so as to not have to clear the variable values with the same execution.
The overall design would be as shown below.
Conclusion
In short, using KingswaySoft, you could easily implement such cascaded logic in a simple and effective way. Dynamic and easy to configure, the component can bring down the design load to a simple and straightforward data flow to perform multiple actions.
We hope the above has helped!