Documentation forTask Factory

Task Factory XML

 Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.

XML Destination

Important:  XML Destination is included in all releases for backwards compatibility, but has been discontinued for future development. It's recommended that users choose the XML Generator Transform or the XML Output Destination.
Destination Icon Destination Description
Task Factory Xml Destination Icon The XML Destination is used to export XML data within an SSIS package.

General

Task Factory XML Destination General

Output File Settings

Option Description
Output File Connection The name of the file connection manager where the file is output. You can use either a flat file or file connection type.
Output Path Stored In Variable Check this option to tell the XML Destination that the path of the outputted XML file is contained in the variable selected in Output File Path Variable.
Output File Path Variable The name of the variable that contains the path where the XML file is output.

Mappings

Option Description
Include Check the box in this column if the data from the respective source column is included in the outputted XML file.
Input Column Read only column that displays the source column that the row is for.
XML Name The name of the element or attribute that is output to the XML file.
Style
  • Attribute
  • Element
  • Element with cdata
  • Element with XML data
Null Handling Define how to handle null source data:

  • Output empty attribute / element - Outputs empty element or attribute.
  • Don't Output attribute / element - The XML data is ignored and not output.
  • Replace null data with user defined value - Replaces null data with a user defined value.
Order The order that the data is output to the XML file.

Advanced 

Task Factory XML Destination Advanced

XMLDocument Settings

Option Description
Root Document Element Name The name of the root XML element.
Root Document Element Namespace The name of the root XML element namespace.
Row Element Name The name of the row XML element.
Row Document Element Namespace The name of the row XML element namespace.
Remove Invalid Characters? Selecting this option removes invalid characters from the source data before exporting it to XML.
Don't Write Empty File? Selecting this option does not write anything when there is no output from the source.

Formatting Settings

Option Description
Encoding The encoding of the outputted XML file.

  • UTF-8
  • Unicode
  • UTF-32
  • ASCII
Indent Elements? This option indents and adds a newline to each element of a row of data.
Add newline after each attribute This option adds a newline to each attribute of a row of data.

Note:  This is only available with indent elements.
Add newline after each row This option adds a newline after each row of data.

Note:  This is only available if indent elements is not selected.
Output XML Header Determines whether or not the XML header is output with the XML file.

Preview Output

Task Factory XML Destination Preview Output

This tab previews the data that is output in the XML file.

XML Output Destination 

Destination Icon Destination Description
Task Factory XML Output Destination Icon The XML Output Destination is used to generate and write XML from one or more source inputs using an XSD mapped to input columns. Similar to the XML Generator Transform, users should opt for the XML Output Destination when writing XML files that use large datasets.

Requirements

  • Valid XSD file
  •  When using more than one source, all inputs must be sorted

Task Factory Xml Output Destination Map Elements

Steps

Option Description
Map Elements In this step users can map columns to elements in the right.
Define Input Relationships In this step the right side section changes to Map Input Key Columns.

Output Connection Manager

Option Description
Connection Create or choose a connection to the output XML file.

Map XML Elements

After selecting the XSD file to use, the field populates.

Option Description
Choose XSD File Allows users to select an XSD file.
Auto Map Elements Selecting this auto maps elements and/or attributes to inputs with the same name.
XML Data This is a read only view of the elements and attributes from the XSD.
Group This is where you assign sequence elements an input to group the sequence on.
Data The column for users to map XSD attributes and elements to input columns.
XML Writer Options
  • Use Pretty Formatting - Toggle for output to use line breaks and indentations or a single line . 

Warning:   : Pretty Formatting can increase file sizes.

  • Check Characters - Enabling this option fails the component when illegal characters are output.
  • Omit XML Declaration - The opening XML declaration. <?xml version="1.0" encoding="utf-16"?> is removed.
  • Newline On Attributes - All attributes included in the output will be printed on a new line.
  • Encoding - Select from the following options the output encoding:
    • UTF8
    • Unicode
    • UTF without BOM (Byte Order Mark)
  • New Line Handling
    • Entitize -  Replaces new line characters that would not be otherwise preserved with character entities.
    • Replace -  Replaces new line characters with \r\n.
    • None -  Leaves the input unchanged.
Additional Information: For more information to determine which handling should be used, see Microsoft's help file XmlWriterSettings.NewLineHandling Property.

Define Input Relationships

Task Factory Xml Output Destination Define Input Relationships

If multiple inputs are attached to the XML Output Destination and the selected XSD has parent-child sequences, users need to define these input relationships. This can be done by selecting the Parent Input and Child Input, dragging the relational key between the two, and then selecting the Add button.

Guid Key Sort Algorithm

Identifies the method in which the input data is sorted when using Guid as relationship keys. 

Note:  This option only appears when Guid keys are used.

The following methods can be used:

Method Description
SQL Server Order By
Performed by using an ORDER BY clause within a query. Additionally, the IsSorted property need to be set to true and the key sort order set. These are found in the source(s) Advanced Editor (eg Ole DB Source and ADO.Net Source).
Sort Transform
Configured when using the SSIS native Sort Transform component between the source(s) and XML Output Destination.
.NET Guid
Configured when using .NET Guid sorting within an upstream Script Task.

XML Generator Transform

Important:  The XML Generator processing occurs in memory, therefore, it's not recommended for large documents.
Transform Icon Transform Description
Task Factory XML Generator Transform Icon The XML Generator Transform is used to generate XML from one or more source inputs using an XSD mapped to input columns. It's used for producing hierarchical XML content that will not be stored in a file.

Task Factory Xml Generator Transform Map Elements

Option Description
Steps There are two steps:

  • Map Elements - In this step the right side section is Map XML Elements.
  • Define Input Relationships - In this step the right window changes to Map Input KeyColumns.
Use Temporary File This option is used to relieve memory pressure when using larger datasets. When this option is selected, each buffer writes to a temporary file, and then is sent to the XML file while reading the next buffer.  

Note:  For extremely large datasets, it is recommended to use the XML Output Destination instead of the transform.
Use Variable As Path Select this option to set the Temporary File's path within a variable.
Location Defines the path to the Temporary File when not using a variable.
Use Pretty Formatting Toggle for output to use line breaks and indentations or a single line.

Warning:   Pretty Formatting can increase file sizes.
Check Characters Enabling this option fails the component when illegal characters are output.
Omit XML Declaration The opening XML declaration. <?xml version="1.0" encoding="utf-16"?> is removed.
Newline On Attributes All attributes included in the output print on a new line.
Output Encoding Selects the output encoding:

  • UTF8
  • Unicode
  • UTF8 Without BOM (removes the byte order mark)
New Line Handling
  • Entitize -  Replaces new line characters that would not be otherwise preserved with character entities.
  • Replace -  Replaces new line characters with \r\n.
  • None -  Leaves the input unchanged.
Additional Information: For more information to determine which handling should be used, please see XmlWriterSettings.NewLineHandling Property.

Map XML Elements

After selecting the XSD file to use, the field populates.

Option Description
XML Data This is a read only view of the elements and attributes from the XSD.
Group This is where you assign sequence elements an input to group the sequence on.
Data This is where you map XSD attributes and elements to input columns.

Define Input Relationships

If there are multiple inputs attached to the XML generator transform and your XSD has parent-child sequences, you need to define input relationships if you mapped inputs to those child sequences.

Task Factory Xml Generator Transform Define Input Relationships

XML Source

XML Source is a native SSIS component.

Additional Information: See the XML Source article on Microsoft Docs for details.