SSIS XML Source (File, SOAP, REST API)

SSIS XML Source can be used to consume multiple XML files or XML formatted response from SOAP Web service or REST API Web Service. It supports advanced filtering along with flexible approach to configure request parameters for web service.

Features

Read XML data from any SOAP/REST API Web Service using methods such as GET/POST.
Read XML data from single or multiple XML files (use of wildcard allowed. e.g. c:\data\*.xml)
Unparalleled performance with large file support (See how we processed 3 million rows in 3 mins)
Support for Path expression to extract data from any level (e.g. Extract Orders nested under Customer Node).
Support for passing custom headers to SOAP or REST Web service
Support for OAuth 1.0 and 2.0 security standard which is adopted by major cloud services
Call API using Dynamic Token Authentication or Basic authorization (i.e. UserID / Password)
Support for looping through multiple files using wildcard pattern (e.g. *.xml).
REST API Pagination Support – Loop through multiple requests (explained here and here)
Support for HMAC Authentication (SHA256, SHA512, MD5)
Support for passing cookies
Allow to cancel infinite pagination on UI Save
Option to parse output from command line Standard Output Stream (e.g. cmd:>curl -k http://httpbin.org/get)
Allow Regular Expression in Filter (e.g. $.store.books[?(@author =~ /^sam$/ )]
Expose MatchForEqual Option for ContineOnErrorForStatusCode [Continue on error with specific response status code] on Error Handling Tab
Support for SQL Server 2022, 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory
Articles/Tutorials: XML related articles

Watch this video on YouTube

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

SSIS XML Source – Direct Mode – Call REST API web service or read from XML File

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

SSIS XML Source – Read data from XML files (Single or Multiple files) – Use wildcard pattern in path

SSIS XML Source Package Execution Log

Direct Value – Read from XML String

SSIS XML Source – Direct mode (Read XML String or Variable)

File Path or URL from Variable – Read XML String stored in SSIS variable

SSIS XML Source – Variable Mode – Call Web API or Read from File Path stored in SSIS Variable

Using Filter to extract / de-normalize nested hierarchy (Extract data from Array)

SSIS JSON Source supports powerful feature to visualize the data structure of your JSON file or REST API. You can select any hierarchy you like to extract using select filter UI. Notice that JSON array is indicated using the different icon.

REST API Pagination Options

REST API Pagination Options – Paginate via URL Parameter, Paginate via Body Attribute, Paginate via HTTP Header value and more. Along with different pagination mode you will also find many options to detect pagination end condition.

Select XML Output columns, Edit SSIS Metadata for XML Output

Advanced SSIS Metadata Editor with many features – Auto detect columns and datatype. Option to change scan row count. Select Columns for output, option to lock manual changes (prevent overwrite)

Extract Multiple Outputs from XML (using XML Source and XML Parser Transform)

Extract multiple array output from XML file / REST API response in (SSIS XML Source, XML Parser Transform)

Extract multiple array output from XML file / REST API response in (SSIS JSON Source, JSON Parser Transform)

SSIS Example: Loading XML to SQL Table with Error Handling

SSIS XML Source Example – Loading XML to SQL Table with Error Output (Redirect bad records to Error Output)

Articles / Tutorials

Click here to see all articles for [SSIS XML Source (File / SOAP)] category

Create Excel File in SSIS (Read from JSON / XML)

Introduction In this post, we will learn how toÂ Create Excel File in SSIS from source like JSON / XML.Â We will useÂ SSIS PowerPackÂ to connect and query a JSON or XML file. This article also covers creating Excel from JSON File. JSON stands for JavaScript Object Notation and it is an Open and Standard format to […]

How to read RSS feed in SSIS and ODBC (with pagination)

Introduction Read RSS feed in SSIS can be challenging. RSS named first RDF Site Summary and later named Rich Site Summary and Really Simple SyndicationÂ allows customer applications to be updated with the news of a site. For example,Â Microsoft RSS feeds,Â Apple RSS feeds,Â Samsung RSS feeds,Â etc.Â With RSS the information comes to you directly and you do […]

Read SAP S4 / HANA data in SSIS (OData REST API)

Introduction In out previous post we saw how to call REST API in SSIS. Now let’s learn how to read data from SAP S4 / HANA OData Service (i.e. S/4HANA). SAP HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use SSIS XML SourceÂ component to read SAP […]

Call Oracle UCM Web Service in SSIS (Read XML SOAP API)

Introduction In this post we will learn how to access data from Oracle UCM Web Service (Middle layer for WebLogic) and load into SQL Server or any other target. We will use SSIS XML Source to achieve this result. About Oracle UCM Web Service If you are not sure what is SOAP Web […]

How to read data from QuickBooks Online in SSIS

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implementÂ QuickBooks Online APIÂ Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) usingÂ SSISÂ in few clicks. We will useÂ SSIS XML Source ConnectorÂ toÂ Read data from QuickBooks OnlineÂ and Load into SQL Server / other targets (Using OAuth Connection). We […]

How to read data from NetSuite in SSIS (SimpleTalk SOAP API)

Introduction In this post we will learn how to read data from NetSuite in SSIS. We will use ZappySys XML Source for SOAP API access. What is NetSuite CRM? NetSuite is a CRM / ERP product. It gives you scalable cloud CRM / ERP solution targeted at high-growing, mid-sized businesses and large enterprises. […]

How to call SOAP / REST API using Dynamic Token in SSIS

Introduction In this blog, we will learn how to call SOAP / REST API using Dynamic Token in SSIS (i.e. Two steps authentication approach – Fist Call Login API to get token and then call API). In our previous blog post, we saw how to call Web API using some industry standards approaches, such as […]

How to Convert XML into JSON using SSIS

Introduction These days, JSON is more popular and it is replacing XML because it is faster, easier to use, it is shorter because it does not require tags and uses brackets instead. In this tutorial, we will learn how to convert XML into JSON using SSIS. So let’s get started. Requirements SSDT for business intelligence […]

Load SQL Server data to Workday using SSIS / SOAP API

Introduction In our previous article, we saw step-by-step approach to read data from workday using SSIS. In this article, we will focus on how to load SQL Server data to Workday (e.g. POST, Create, Update). We will useÂ SSIS Web API DestinationÂ and the combination of other Transforms such as SSIS Template Transform and SSIS XML Generator […]

How to read large XML / JSON file in SSIS (3 Million Rows in 3 Mins)

Introduction In this post we will learn how to use ZappySys SSIS XML SourceÂ or ZappySys SSIS JSON SourceÂ to read large XML or JSON File (Process 3 Million rows in 3 minutes – 1.2 GB file). If you use default settings to read data then it may result into OutOfMemory Exception so we will outline […]