in this part I will show how to generate quite a simple XML file. In the next part
we will construct a complex XML. To start create a new SSIS package and drag and
drop export XML tasks from SSIS toolbox into the control flow then double click
on export XML tasks for editing. First of all you will need to create a connection
select either a OLEDB, ADO.net or ODBC type of connection. We will use OLEDB
connection.
Once you have created the connection you can simply enter the tables you want
export. Separate table names with the pipe symbol or check the example below
to see how to select tables for export using a pattern search.
You can also exclude tables or views from pattern search.
using customers pipe orders percent will export three tables as is each to a
different file however if you need a custom XML layout you will need to use
custom layout option. Let's say you have the table orders with this data and want
to produce this kind of XML. Xml's outer element is request import orders an
element has a static attribute version with value 1.0 with an import orders there
is a static element add only with value true then orders element
embraces all order elements each order element has an attribute idea with its
actual ID inside each order element you have all the details of an order to
begin go to tab custom layout check single data set Airy I will explain why
we need it in a moment right click on data sets and click add data set
enter a data set name row element name select the connection and table orders
you
you can also use a sequel query to define a data set if your sequel query
is dynamic then you can use an SSI as variable in this demo we will use a
table
right click on mappings and select add unbound nested element menu item to add
XML element named import orders
right-click on element import orders and select add attribute to add version
attribute once the window appears select add static value option and fill in a
name and a value
again right-click on element import orders but this time select add static
value element to add add only element then fill in a name and a value.
to add orders under import orders element once again right click on import
orders and select add document add a menu item.
Now we can get back to single data set re option. By default ad document adding
requires two data sets parent and child which we will use in the next part of
the video thus we needed it to produce an array of
elements using just one data set. To proceed enter orders for element name
and order for row element name.
now right-click on item orders and select add attribute menu item this will
add ID attribute select order ID as source column and then change output
alias to ID to match the name of the attribute.
it's time to add order elements right-click on orders and select add
element use add multiple option and select the required columns.
To change ordered on nodes name to match the one in the outfit X amount
right-click on the node and change the output alias to date.
you now go to XML options tab to update
order date format
the last modification we need to do is change route elements named to request
among many options in XML options tab you can change file encoding or
character set do not output properties will no value and do not output XML
declaration node with export XML task you can split output XML just go to
split options tab and enable the option to split by file size or by rows number
finally go to target tab and select the target destination of your XML. You can
either directly enter a path into the text box or select a variable which is
set to a path.
you can also use a target connection using a connection enables you to save
to a flat file, Amazon s3 storage, Azure blob storage FTPS or SFTP. In this
scenario we will use a direct path
export XML tasks also supports overwriting target file appending to an
existing file or compressing a file.
let's open destination folder and execute the task.
finally let's compare the generated XML with the result we expect using visual
studio code a popular text editor.
as you see both xml's are identical this concludes export XML task demonstration
and how to generate a simple XML file in the next part you will learn how to use
XML file destination to generate a more complex XML file