SSIS tutorial: How to export SOAP web service data to Excel

Tutorials SSIS Tutorials
, ,
1

Introduction

This article will show how to extract data from a SOAP Web Service and export it to an Excel file using SSIS. We will use the XML Source to call the SOAP endpoint and the Excel Destination to generate the output file.

Prerequisites

Steps

  1. Drag and drop a Data Flow task into your SSIS package.

    Drag and drop Data Flow
    Drag and drop Data Flow565×272 22.1 KB

  2. Inside the Data Flow, add an XML Source component.

    Drag and drop XML source
    Drag and drop XML source667×326 35 KB

  3. Open the XML Source and enter the URL or a file with XML format. In this example, we will use the following URL:
    http://webservices.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL

  4. If you have a different SOAP example, you may also need to specify additional settings such as HTTP Request Method, Request Body, and required Headers and authentication.

  5. Enter the array filter to extract the specific object you want. In our case, we will use:
    $.definitions[*].portType[*].operation[*]

  6. Click Preview to validate the SOAP response and confirm that the XML structure is correct. Click OK to save the configuration.

    image
    image827×725 67.1 KB

  7. Go to Connection Manager, right-click, and select New Connection, then choose ZS-EXCEL.

  8. Select an existing Excel file and enter a password if required. Test the connection, then click OK to save it.

    image
    image775×500 44.7 KB

  9. Drag and drop ZS Excel Destination component from the SSIS toolbox onto the Data Flow and connect it to the XML Source.

    Drag and drop Excel Destination
    Drag and drop Excel Destination669×343 35.7 KB

  10. Open the Excel Destination and select the Excel connection created in the previous step.

    image
    image713×358 11.8 KB

  11. Go to Component Properties and select the sheet you want to export to. You can configure additional settings such as Overwrite if needed.

    image
    image723×664 33 KB

  12. Go to the Mappings tab and ensure all columns from the XML Source correctly map to the Excel columns.

    image
    image723×624 30.9 KB

  13. Click OK to save the configuration.

  14. Run the SSIS package to export the SOAP Web Service data into the Excel file.

    image
    image262×187 10.4 KB

Video tutorial

Conclusion

By following these steps, you can easily extract data from a SOAP Web Service and export it to an Excel file using SSIS. With ZappySys SSIS PowerPack, you can efficiently connect to SOAP endpoints, parse XML responses, and automate your data integration workflows.

Explore our ZappySys SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions.

References

Contact us

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