Consume XML Web service in SSIS (REST API or SOAP)

Introduction

Microsoft provides native XML Source adapter but most of users will find it totally unusable in many scenarios. In this post you will learn how to read data from XML files or consume XML web service in SSIS using ZappySys SSIS XML Source. You will also learn how to overcome many shortcoming in Microsoft SSIS XML Source in few clicks.

What is missing in Microsoft SSIS XML Source

Sometimes you will face too much trouble when using out of the box Native SSIS XML Source. Few points highlighted below.

• Microsoft SSIS XML Source Can’t consume XML data from Web service (such as SOAP Web service or REST API Web service)
• Using Microsoft SSIS XML Source you Can’t read data from multiple XML files easily (e.g. c:\cust*.xml)
• If you want to de-normalize nested datasets then you cant do either. If you have 10 nested levels then Microsoft SSIS XML Source will produce 10 Outputs and making it totally useless)
• Microsoft SSIS XML Source – Doesn’t support XPath like expressions
• No support for parent-child relationship linking in Microsoft SSIS XML Source

ZappySys SSIS XML Source for flexible data extract

ZappySys SSIS XML Source is one of many Adapters/Tasks which comes with SSIS PowerPack. ZappySys takes very unique and innovative approach compared to Microsoft native SSIS XML Source.

Unlike Microsoft XML Source ZappySys XML Adapter doesn’t produce multiple outputs if you have nested sections but it produces Flat Dataset which is highly de-normalized. It includes parent columns and child columns in the same row e.g. Customer info +  order info if you have orders nested inside customer node.

It allows you to control which section you want to extract using Filter expression (Think like XPath but not exactly same syntax. Its similar to JSONPath expression language). You can also consume local XML files or consume Remote Web service in XML format.

Direct URL – Read from XML Web service URL – Call XML REST API Service and get de-normalized resultset

Direct File Path – Read data from XML files (Single or Multiple files) – Use wildcard pattern in path

Direct Value – Read from XML String

Conclusion

Consuming XML Web service in SSIS can be tricky but using ZappySys SSIS XML Source you can surely cut down most of you headache. It supports multiple modes of consuming local XML files or Web Service API. It’s innovative approach of de-normalization can give you big advantage compared to Microsoft Native SSIS XML Source.

Download SSIS PowerPack