SSIS tutorial: How to upload multiple files to REST API without programming

SSIS PowerPack
, ,
1

Introduction

The Foreach Loop Container in SSIS is a powerful control flow tool used to iterate over collections of items, such as files, database records, or API responses. When combined with ZappySys SSIS PowerPack, it enables seamless automation for dynamic ETL scenarios.

In this tutorial, you’ll learn how to use the Foreach Loop with ZappySys components to iterate over a list of files and upload them via a REST API Task.

Prerequisites

Steps

Step 1: Create Required Variables

First, we will create some SSIS variables.

Go to SSIS > Variables and define the following:

Variable Name Type Description
User::ListFile Object Stores the list of files
User::FullPath String Stores the current file’s full path
User::FileName String Stores the current file’s name

Step 2: Get the List of Files with Advanced File System Task

  1. Drag ZS Advanced File System Task onto the Control Flow.

  2. Set Action to Get File List as ADO.net DataTable.

  3. Specify your folder path and use a wildcard filter (e.g., C:\MyFiles\*.json).

  4. Output the results to the User::ListFile variable.

  5. Click OK to save.

    Advanced File System Task
    Advanced File System Task586×500 34.6 KB

Step 3: Configure Foreach Loop Container

  1. Drag a Foreach Loop Container and connect it to the previous task.

  2. Double-click it and go to the Collection tab.

  3. Set Enumerator to Foreach ADO Enumerator.

  4. Set the source variable to User::ListFile.

    Foreach Collection
    Foreach Collection739×633 25.8 KB

  5. Go to the Variable Mappings tab:

    • Index 0 → User::FullPath
    • Index 1 → User::FileName

  6. Click OK to save.

    Variable Mapping
    Variable Mapping737×608 23.8 KB

Step 4: Log File Information (Optional)

  1. Inside the Foreach Loop, drag a ZS Logging Task.
  2. Log the variables User::FileName and User::FullPath to verify each iteration.
  3. Save and close the configuration.
    Logging Task
    Logging Task573×483 33.3 KB

Step 5: Upload Each File Using REST API Task

Finally, we will upload multiple files using the REST API task.

  1. Add a ZS REST API Task inside the Foreach Loop.

  2. Configure it to perform a POST or PUT request.

  3. Pass variables dynamically:

    • Use @[User::FileName] in the URL or query string.
    • Use @[User::FullPath] in the body to upload the file.

  4. Refer to this detailed guide on file upload.

    REST API Task
    REST API Task837×725 49.4 KB

  5. Run the package. You should see each file’s name logged, followed by an API call to upload the file.

    Execution Result
    Execution Result912×343 40.3 KB

Benefits of Using ZappySys with Foreach Loop

  • Dynamic automation – easily process multiple files or records.
  • REST support – handle APIs with no custom code.
  • Flexible file handling – supports wildcard filters and recursion.
  • Real-time monitoring – track each iteration using logging tasks.

Conclusion

The Foreach Loop Container combined with ZappySys SSIS components offers a powerful way to build flexible and scalable ETL workflows. Whether you’re integrating file uploads, processing dynamic API calls, or looping through records, this approach reduces manual effort and increases efficiency.

Visit our official page to explore more SSIS components, automation features, and real-time data integration tools included in ZappySys SSIS PowerPack .

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.