How to POST many requests to REST API in SSIS without loop task

SSIS General How To
, , , ,
1

Introduction

When working with APIs, it is common to receive a list of IDs that need to be updated individually. One approach is using a Foreach Loop to send a request for each ID, which can be inefficient. This article demonstrates a more optimized way to achieve this using SSIS and ZappySys tools. By leveraging the JSON Generator Transform and the Web API Destination, you can dynamically update records with minimal effort while improving performance.

Prerequisites

Steps

  1. Add a Data Flow Task to your SSIS package.

  2. Drag a XML Source component into the Data Flow. Provide a valid URL or local XML file. The columns we use are ID, Name, and Status, where we will update the Status.

    XML source example
    XML source example828×745 36.2 KB

  3. Connect the source component to a generation component, depending on your API format. You can choose from:

  4. Inside the JSON Generator Transform, construct the JSON structure by adding the necessary columns and the ID for the update.

  5. Right-click on the ID field and select Edit. Then, check the Include Column in the Downstream option. This allows the ID to be sent as a variable from the JSON Generator Transform to the Web API destination.

  6. Optionally, you can check the Hide from Output option. This will remove the ID from the output while keeping it as a variable.

    JSON generator example
    JSON generator example738×625 46.4 KB

  7. Connect the JSON Generator Transform component to the Web API Destination. Create or select the required connection and add the input column for the request body. This ensures that the JSON Generator Transform output is sent as the body of the API request.

  8. In the URL, create a dynamic segment with the ID as a variable. This allows each request to be sent with the respective ID. You can manually write <%id%> or edit the URL to use placeholders from the Columns folder.

    Web API destination example
    Web API destination example723×775 53.6 KB

  9. Finally, run the package to dynamically execute the API calls for each ID.

    Final result
    Final result299×405 17.1 KB

Conclusion

Following these steps, you can efficiently update multiple records using Foreach Loop processing in SSIS. You can dynamically structure your requests by leveraging the ZappySys JSON Generator Transform and Web API Destination, reducing manual effort and improving automation. This approach enhances API integration and ensures smooth data updates in your workflows.

To get started, download and install the ZappySys SSIS PowerPack. If you have any questions or require further assistance, feel free to contact ZappySys Support.

References

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.