In general, HTTP service calls are facilitated by standard HTTP headers with the necessary request body. The HTTP headers might include authentication information, with a very common use case being the OAuth Bearer token used for server authentication. The request body contains the payload data that needs to be processed by the server, usually in a JSON or XML format. However, we have recently come across a special client case in which both the authentication information and the request payload need to be sent in the body using an x-www-form-urlencoded format. We will show you how this can be done with ease using KingswaySoft's SSIS Productivity Pack product.
For this demonstration, we will be using the following components:
- HTTP Connection manager
- XML Source component
- Postman tool for validation
Setting up authentication in Postman
Let's look at the screenshot from Postman below, where we have configured the authentication for the endpoint you are working with. As you can see, the access_key and secret_key are in the body of the POST request, along with another parameter called the decoder_query.
Now, to execute this service call in SSIS, you will first need to obtain the original HTTP request body, which is the merged format used for the actual request. To do this in Postman, go to the top-right corner, click on the code snippet drop-down, and select HTTP. As shown below, line number 7 contains the body that you can copy and use in the subsequent steps.
Configuring SSIS data flow
Once you have saved the HTTP code snippet, open the KingswaySoft HTTP Connection Manager in your SSIS package and enter the base URL. Since you do not have any additional authentication headers (apart from the default ones), you can choose the authentication mode as "None".
And then, save and close the connection manager by clicking on the 'Ok' button, and open the XML Source component. Typically, to send a body in a request, you would use the XML Destination component. However, in this case, you not only need to send the request but also parse out the response. In the XML Destination, you can use the Output columns option; however, an easier approach to parsing out data on a larger scale would be to use an XML Source component. Please find our Online Help Manual (link here) to learn more about using Output Columns in the XML Destination component, if you'd like to read further on it.
Now, once the XML Source component is chosen, you can set the HTTP method to POST, which will enable the request body tab in the component.
And here, you can enter the XML body as displayed in the example below. As you can see, we have encoded the XML query using the x-www-form-urlencoded format.
You could then utilize the document designer page to import a design that corresponds to the response and parse out the data as required. Another thing worth mentioning here is that if you have a specific parameter in the request body that needs to be dynamic, you can easily parameterize the XML string using the expression editor. Simply click on the "fx" button in the "request body" space (bottom-right) and work with the expression editor to concatenate a variable or a parameter to the string.
Conclusion
In short, by using KingswaySoft HTTP and XML components, you can simplify and dynamically process special cases or requests, such as the one mentioned above, with ease. We have a more detailed blog post on how the parameterization of the components works. Click here if you are interested in learning more.
We hope that this helps!