Task Factory XML

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.

XML Destination

Destination IconDestination Description
Task Factory Xml Destination IconThe XML Destination is used to export XML data within an SSIS package.

General

Task Factory XML Destination General

Output File Settings

OptionDescription
Output File ConnectionThe 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 VariableCheck 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 VariableThe name of the variable that contains the path where the XML file is output.

Mappings

OptionDescription
IncludeCheck the box in this column if the data from the respective source column is included in the outputted XML file.
Input ColumnRead only column that displays the source column that the row is for.
XML NameThe name of the element or attribute that is output to the XML file.
Style
  • Attribute
  • Element
  • Element with cdata
  • Element with XML data
Null HandlingDefine 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.
OrderThe order that the data is output to the XML file.

Advanced 

Task Factory XML Destination Advanced

XMLDocument Settings

OptionDescription
Root Document Element NameThe name of the root XML element.
Root Document Element NamespaceThe name of the root XML element namespace.
Row Element NameThe name of the row XML element.
Row Document Element NamespaceThe 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

OptionDescription
EncodingThe 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 attributeThis option adds a newline to each attribute of a row of data.

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

Note:  This is only available if indent elements is not selected.
Output XML HeaderDetermines 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 

XML Output Destination

Destination IconDestination Description
Task Factory XML Output Destination IconThe 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

OptionDescription
Map ElementsIn this step users can map columns to elements in the right.
Define Input RelationshipsIn this step the right side section changes to Map Input Key Columns.

Output Connection Manager

OptionDescription
ConnectionCreate or choose a connection to the output XML file.

Map XML Elements

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

OptionDescription
Choose XSD FileAllows users to select an XSD file.
Auto Map ElementsSelecting this auto maps elements and/or attributes to inputs with the same name.
XML DataThis is a read only view of the elements and attributes from the XSD.
GroupThis is where you assign sequence elements an input to group the sequence on.
DataThe 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:

MethodDescription
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.

XML Generator Transform

Transform IconTransform Description
Task Factory XML Generator Transform IconThe 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

OptionDescription
StepsThere 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 Key Columns.
Use Temporary FileThis 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 PathSelect this option to set the Temporary File's path within a variable.
LocationDefines the path to the Temporary File when not using a variable.
Use Pretty FormattingToggle for output to use line breaks and indentations or a single line.

Warning: Pretty Formatting can increase file sizes.
Check CharactersEnabling this option fails the component when illegal characters are output.
Omit XML DeclarationThe opening XML declaration. <?xml version="1.0" encoding="utf-16"?> is removed.
Newline On AttributesAll attributes included in the output print on a new line.
Output EncodingSelects 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.

OptionDescription
XML DataThis is a read only view of the elements and attributes from the XSD.
GroupThis is where you assign sequence elements an input to group the sequence on.
DataThis 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