XML Output Destination
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.
• Valid XSD file
• When using more than one source, all inputs must be sorted
o Map Elements - In this step users can map columns to elements in the right
o Define Input Relationships - In this step the right side section will change to "Map Input Key Columns"
• Output Connection Manager
o Connection - Creates or choose a connection to the output XML file
• Map XML Elements - After selecting the XSD file to use, the field will populated
o Choose XSD File - Allows users to select an XSD file
o Auto Map Elements - Clicking this will auto map elements and/or attributes to inputs with the same name
o XML Data - This is a read only view of the elements and attributes from the XSD
o Group - This is where you will assign sequence elements an input to group the sequence on
o Data - The column for users to map XSD attributes and elements to input columns
o XML Writer Options
■ Use Pretty Formatting - Toggle for output to use line breaks and indentations or a single line (caution: Pretty Formatting can increase file sizes)
■ Check Characters - Enabling this option will fail the component when illegal characters will be outputted.
■ 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:
• 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.
For more information to determine which handling should be used, please see Microsoft's help file at https://msdn.microsoft.com/en-us/library/system.xml.xmlwritersettings.newlinehandling(v=vs.110).aspx
• Define Input Relationships - If multiple inputs are attached to the XML Output Destination and the selected XSD has parent-child sequences, users will 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 clicking the Add button.
• Guid Key Sort Algorithm - Identifies the method in which the input data is sorted when using Guid as relationship keys. (This option only appears when Guid keys are used.) The following methods can be used:
o SQL Server Order By - Performed by using an "ORDER BY" clause within a query. Additionally, the "IsSorted" property will 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.)
o Sort Transform - Configured when using the SSIS native "Sort Transform" component between the source(s) and XML Output Destination.
o .NET Guid - Configured when using .NET Guid sorting within an upstream Script Task.