Content

• Video Tutorial
• Step-By-Step
• Properties
• Setting UI
• See Also

Video Tutorial

Below video is showing XML Generator Transform UI rather than XML File Destination but concept / UI to generate nested XML is almost identical in both components. In XML Generator you have Output column for XML and with XML File destination you have to choose File Connection as destination.

Step-By-Step

In this tutorial we will see how to extract data into XML File Format from multiple source(parent-child relationship). You can use SQL Server Tables source for generate XML file. In this tutorial we are going use ZS DummyData Source. This is same as XML Generator Transform.

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
4. Double click on the Data Flow task to see Data Flow designer surface.
5. Here, In Visual Studio, drag and drop the DummyData Source and XML File Destination in the design surface and join the tasks with the blue arrow.
   
6. Here, you can use our OLE DB Data Source too. Click here for more information.
7. But, we are going through DummyData Source and File Connection for XML File Destination.

How to create File Connection for XML File Destination.

1. Let's, Right click on Connection Managers Panel to Create File Connection, so you can use Destination Path, and Context Menu will appear, Select New Connection from the Context Menu. In the File Connection Manager, select the Usage type and specify the path of the XML file.
   
2. Click on OK button to save connection configure setting UI.

How to Generate XML File using XML File Destination.

1. Double click on DummyData Source to configure it, select Customer and Orders profile from Template dropdown.
2. Once you change Template you will notice that lot of columns automatically added.
3. Double click on XML File Destination to configure it, Select File Connection we have already created. Check on Overwrite target file. Set Output to Mode Output One XML Document for each parent record and Check on Indent Output (pretty print).
   
4. Now, Right Click on Mappings from Left Panel of XML File Destination, Select @Add Attribute(s) (Below this node). And then, In Add/Edit Attribute dialogbox Select Add Multiple (Bound) and check on Columns for Data.
   
5. Click on OK to save attribute Settings.
6. Now, Right Click on Mappings from Left Panel of XML File Destination, Select Add Element(s) (Below this node). And then, In Add/Edit Attribute dialogbox Select Add Multiple (Bound) and check on Columns for Data.
   
7. Click on OK to save attribute Settings.
8. Now, Right Click on Mappings from Left Panel of XML File Destination, Select Add Document Array (Below this node). And then, configure Add/Edit Element (Array of rows) like below image.
   
9. Click on OK to save attribute Settings.
10. Now, Right Click on Orders from Left Panel of XML File Destination, Select Add Element(s) (Below this node). And then, In Add/Edit Attribute dialogbox Select Add Multiple (Bound) and check on Columns for Data.
    
11. Click on OK to save attribute Settings.
12. Thats all, Just Save and Run or Execute the Project.
    

How to Edit Dataset.

1. Double click on XML File destination to configure it.
2. Select Dataset and Right Click on it, Select Edit, You can change setting in Add/Edit Dataset dialogbox.
   
   
3. Click on OK to save Settings.

Properties

Property Name	Description
LoggingMode	LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log. Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Normal [0] Normal Medium [1] Medium Detailed [2] Detailed Debugging [3] Debugging
PrefixTimestamp	When you enable this property it will prefix timestamp before Log messages.
OutputMode	Determines XML format and output mode (e.g. one XML for all input rows or one XML for each input row) Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description SingleFileFormat [0] Output One Xml Document for all records (Batching Allowed) MultiFileFormat [1] Output One Xml Document for each parent record SingleDatasetArrayFormat [2] Single Dataset Array (Batching Allowed)
LayoutInfo	Defines shape of output XML string
DoNotOutputNullAttribute	Do not output attribute/element if value is NULL. By default null attribute is output as <Phone xsi:nil="true" />
DoNotOutputEmptyNestedAttribute	Set this option to True if you dont want to output empty nodes of sub document (e.g nested property). This option is only applicable if you enabled DoNotOutputNullProperty. If all child peoprties are NULL for parent node then by default output contains empty nodes (e.g. CountryInfo: { CityInfo: { } } for Json --OR-- <CountryInfo><CityInfo /></CountryInfo> for Xml). You can enable this option to avoid such empty nodes from the output.
IndentOutput	Indent XML Text generated by this transform (Pretty Print)
DateTimeFormat	Default output format for datetime columns
Culture	Culture setting for this task. This option is useful to control output format for certain data types (e.g. number, decimal) based on regional settings. In USA dot is used for decimal indicator but in Germany it may be comma. Leave this blank if you want to use system default settings.
UseColumnForHeaderFooter	Document Header/Footer text come from upstream column rather than direct string
DocumentHeader	Header text for document (Added before each document in the output)
DocumentFooter	Footer text for document (Added after each document in the output)
ArrayBatchSize	Specifies how many rows to include in each batch (Array records for single dataset array mode). 0=Include all in one batch. This setting is only valid for Mode=[Single Dataset Array]
OverwriteIfExists	Overwrite target file if already exists
Append	Append to file if exists else create new file. When this option is true OverwriteIfExists is always true.
Encoding	Specifies encoding for data. If you set CharacterSet property then Encoing is ignored. Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Default [0] Default ASCII [1] ASCII UTF8 [2] UTF-8 UTF16 [3] UTF-16 LE (i.e. Unicode Little Endian) UTF32 [4] UTF-32 UTF8WithoutBOM [5] UTF-8 Without BOM UTF32WithoutBOM [6] UTF-32 Without BOM UTF7 [7] UTF-7 UTF7WithoutBOM [8] UTF-7 Without BOM UTF16WithoutBOM [9] UTF-16 Without BOM BigEndian [10] UTF-16 BE (i.e. Unicode Big Endian) BigEndianWithoutBOM [11] UTF-16 BE Without BOM
CharacterSet	Character set name (i.e. ISO-8859-1) or code page number. If you set this property then Encoding property will be ignored. To output without BOM set this property to blank and use Encoding instead.
DoNotOutputXmlDeclaration	Controls weather you need to output XML declaration node (e.g. <?xml version="1.0" ?>)
XmlStandAlone	Standalone attribute for XML declaration (Leave blank to exclude this attribute). This attribute is usually used when you have DTD validation in XML which is not very common.
Namespaces	XML NameDo not output attribute/element if value is NULL. By default null attribute is output as <Phone xsi:nil="true" />
RootElementName	Document root node (First node after XML declaration)
RootElementPrefix	Prefix for Root element (e.g. enter ns1 if you want your root element as <ns1:Root>
IgnoreStaticAttributeForEmptyNestedPropertyCheck	Ignore static attribute from nested element check when [Do not output empty nested elements] is checked

Setting UI

See Also

• SSIS XML Generator Transform
• SSIS JSON Generator Transform
• SSIS JSON File Destination
• SSIS JSON Source Connector
• SSIS Export JSON File Task
• SSIS JSON Parser Task
• SSIS MongoDB Source Adapter
• SSIS Azure Table Storage Source Adapter
• SSIS Amazon S3 Storage Task
• SSIS Amazon Storage Connection Manager

Articles / Tutorials

Click here to see all articles for [SSIS XML File Destination] category

Calling SharePoint 365 REST API in SSIS Introduction to REST API with SharePoint in SSIS Using REST API with SharePoint in SSIS is a common requirement to Administer SharePoint. SharePoint 365 is a nice Microsoft application in the Cloud used to share documents and collaborate with the company. You can have schedules, projects, documents and more shared using a Web platform. In addition, […]