SSIS XML Generator Transform
 PreviousNext

SSIS XML Generator Transform can generate single XML or multiple XML records from data flow input source(s). This XML output can be stored to file or any Target.

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this tutorial we are going to show you How to Generate single XML File from multiple source(parent child relationship). You can use XML files and SQL Tables source for XML Generator Transform. In this tutorial we are going use ZS Dummy Data Source tables. This is same as JSON 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.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the ZS Dummy Data Source, ZS XML Generator Transform and ZS Trash Destination in the design panel and join the components with the blue arrow.
    SSIS XML Generator - Drag and Drop
  6. Here, you can use OLE DB Data Source, MongoDB Source, and CSV File Source.
  7. Double click on Dummy Data Source For configure it, Check here
  8. Now, double click on ZS XML Generator Transform to configure it.
  9. Now, Right Click on Mappings from left panel of XML Generator Transform, select Add Attribute(s)(Below this node). And than, In Add/Edit Attribute dialogbox, select Add Multiple (Bound) and check on Columns.
    XML Generator Transform - Add/Edit Attribute
  10. Now, Right Click on Mappings from left panel of XML Generator Transform, select Add Element(s) (Below this node). And than, In Add/Edit Element dialogbox, select Add Multiple (Bound) and check on Columns.
    XML Generator Transform - Add/Edit Elements
  11. Here, Right Click on Mappings from left panel of XML Generator Transform, select Add Document Array (Below this node). And than, configure Add/Edit Element (Array of rows) like below image.
    XML Generator Transform - Add/Edit Document Array
  12. Now, Right Click on Orders from left panel of XML Generator Transform, select Add Element(s) (Below this node). And than, In Add/Edit Attribute dialogbox for Select Add Multiple (Bound) and click on Select All columns.
    XML Generator Transform - Add/Edit Sub Elements
  13. Here, You can Add / Edit Dataset Name easily.
    XML Generator Transform - Add/Edit Dataset
  14. Click on OK to save XML Generator Transform UI settings.
  15. Lets Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Drag and Drop
  16. Thats all, Just Save and Run or Execute the Project.
    SSIS XML Generator Execute

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

SSIS XML Generator - Setting UI
SSIS XML Generator - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS XML Generator Transform] category
How to batch REST API requests in SSIS (Bulk Operation)

How to batch REST API requests in SSIS (Bulk Operation)

Introduction In our previous article we saw how to POST data to REST API using few different ways. Now let’s go one step further and discuss another common scenario to batch REST API requests in SSIS. For efficient data transfer many API provides you to submit multiple records in a single request. In this article […]

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 generate XML with namespace / prefix in SSIS

How to generate XML with namespace / prefix in SSIS

Introduction In this post you will see how to generate XML which has namespace and prefix for elements. We will use SSIS XML Generator Transform for this purpose. Common pattern would be generate XML SOAP message using XML Generator Transform and send to Web service using SSIS Web API Destination. For full tutorial on how […]

How to export XML from SQL Server using SSIS

How to export XML from SQL Server using SSIS

Introduction In our previous blog post we saw how to import xml into SQL server using SSIS. In this post we will see how to generate xml in SSIS. If you wish to export JSON rather than XML then check this article To produce complex nested XML you can use following two task/components. Video Tutorial – Create XML […]

Copyrights reserved. ZappySys LLC.