Tutorial - Create XML file using SSIS Task / POST to SOAP API URL / Export XML data from SQL Server

In this video you will learn how to generate a complex XML using SSIS (Create XML file using SSIS Control Flow Task / Data Flow Component). You will find this video helpful if you want to export XML data from a SQL Server (or other source e.g. Oracle, MySQL, DB2) to an XML file, you will also learn how to POST XML data to SOAP Web Service URL or submit XML-based REST API URL..

==============================
SSIS Components used in this Video
==============================
ZappySys PowerPack provides these components to deal with various XML generation scenarios:

* SSIS Export XML Task
* SSIS XML File Destination
* SSIS XML Generator Transform

In case you are watching this video but want to read XML files, use SSIS XML Source component or SSIS XML Parser Transform to parse XML from upstream column.

==============================
SSIS Export XML File Task
==============================
Use Export XML Task when you want to export XML from a database with great performance. Export XML Task supports SQL Server or any other database that you can connect to using ODBC or OLEDB drivers. You can export XML to a file, Azure Blob Storage, Amazon S3 Storage, SFTP or FTPS. With Export XML Task you can export a table as is with minimal configuration or export to a custom layout XML.

Features:

* Export multiple tables/views to XML files (e.g. Sales% or do SalesJan|SalesFeb)
* Export SQL query output to XML file
* Inbuilt Layout Editor for creating complex XML with nested structure (Document Array, Value Array, Nested attributes
* Automatically Split exported XML data into multiple files by Size or Number of records
* Automatically Split exported XML data into multiple files by Split By Column (e.g. SplitBy=Country will create new file for each country)
* Support for Compression and Append
* Create Multi content XML documents
* Support for SQL Server 2017, 2016, 2014, 2012, 2008 (32/64 bit) and now Azure Data Factory
* Articles/Tutorials: XML related articles

==============================
SSIS XML File Destination
==============================
XML File Destination is very similar to Export XML Task, except that it can use anything from upstream in a Data Flow and then save an XML to a file. XML File Destination, as well as Export XML Task and XML Generator Transform, which I will speak about in a moment, all support multiple data inputs. It means that you can have, for example, Customers as one input, Orders as another, and then join these two on a key to write customers with all their corresponding orders as child elements.

Features:

* Generate complex XML document from single or multiple inputs
* Easy to use drag & drop layout Editor for creating complex JSON with nested structure
* Support for SQL Server 2017, 2016, 2014, 2012, 2008 (32/64 bit) and now Azure Data Factory

==============================
SSIS XML Generator Transform
==============================
Finally, use XML Generator Transform when you have data coming from upstream in a Data Flow and yet you still need to pass the generated XML downstream. For example, you want to generate an XML that you need to save to a database or submit it to an XML-based API.

Features:

* Create simple or nested XML documents inside DataFlow Task using simple drag and drop approach
* Ability to create nested XML from Multiple datasets
* Ability to create single XML document for all input records or create one document for each input row from ROOT dataset
* Preview XML structure as you change layout on User Interface
* Ability to join different type of datasets (e.g. SQL Server, Flat File, Excel) to create parent-child relation for nested XML
* Support for SQL Server 2017, 2016, 2014, 2012, 2008 (32/64 bit) and now Azure Data Factory

==============================
SSIS Web API Destination
==============================
SSIS Web API Destination can be used to POST data to any API end point such as JSON API (i.e. POST to REST API) or POST to XML API (i.e. SOAP Web Service). This task provides several advanced options which makes it possible to connect virtually any API Service.

Features:

* POST data to any API endpoint without coding (Read more…)
* Support for windows / basic authentication, client certificate and OAuth (Read more…)
* Support for File upload, REST API POST using Multipart/form-data (Read more…)
* Support for Cookies (Read more…)
* Support for Response Output (includes ResponseText and StatusCode)
* Support for Error Output (Redirect failed request)
* Support for easy Test UI (see request/response)