SSIS XML Source (Read from File, SOAP Web Service or REST API Web Service)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
  • Support for SQL Server 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory
  • Articles/Tutorials: XML related articles

Download Help File Buy
View All Tasks

Featured Articles

ScreenshotsUseful LinksSystem Requirements

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

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 – Read data from XML files (Single or Multiple files) – Use wildcard pattern in path

SSIS XML Source Package Execution Log

SSIS XML Source Package Execution Log

Direct Value – Read from XML String

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

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

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.

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.

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)

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)

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

 

Articles


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


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

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)

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)

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)

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

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)

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

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

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

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)

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 […]

Click here to learn more about System Requirements
Download View All Tasks Like This