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 |
|---|---|
 | The XML Destination is used to export XML data within an SSIS package. |
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 |
|
| Null Handling | Define how to handle null source data:
|
| Order | The order that the data is output to the XML file. |
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.
|
| 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
This tab previews the data that is output in the XML file.
XML Output Destination
| Destination Icon | Destination Description |
|---|---|
 | 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
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 |
Warning: : Pretty Formatting can increase file sizes.
|
Additional Information: For more information to determine which handling should be used, see Microsoft's help file Xml
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 |
|---|---|
 | 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. |
| Option | Description |
|---|---|
| Steps | There are two steps:
|
| 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:
|
| New Line Handling |
|
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.
XML Source
XML Source is a native SSIS component.
Additional Information: See the XML Source article on Microsoft Docs for details.
