SSIS Export XML File Task
 PreviousNext

SSIS Export XML File Task can be used to generate simple or complex XML files out of relational data source such as SQL Server, Oracle, MySQL. In few clicks you can create desired XML Shape from single or multiple tables (Parent/Child Nested Documents)

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

In this section you will learn how to generate XML data file using Export XML file Task (In this case its Default and Custom Layout).
  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. In visual studio just Drag and Drop ZS Export XML Task in the design surface.
    SSIS Export to XML File Task - Drag and Drop
  4. Now, We need OLEDB Connection For Source.

How to create OLE DB Connection.

  1. Let's, Right click on Connection Managers Panel to Create OLEDB Connection, so you can use Source and Context Menu will appear, Select New OLEDB Connection from the Context Menu.
    SSIS Export to XML File Task - Connection
  2. Now, click on New Button for create Connection.
    SSIS Export to XML File Task - Connection
  3. Let's Configure Connection Manager, just Follow steps one by one as we have created.
    SSIS Export to XML File Task - Connection
  4. Click on Test Connection for check correct configure it.
  5. Click OK to Save OLEDB connection manager setting UI.

How to extract data from SQL Server Table to XML file using Export XML File Task (Custom Layout).

  1. Double click on ZS Export XML Task for Configure it.
  2. Check on Custom Layout, Make sure Check on Use Custom Layout from Source Tab to see Custom Layout Tab until it will hide from Properties.
    SSIS Export XML File Task - XML Layout Editor
  3. Now go to Custom Layout tab and Add Dataset with just few clicks.
    SSIS Export XML File Task - XML Layout Editor
  4. Now, Right Click on Mappings from left panel of Export XML File task, select Add Attribute(s)(Below this node). And than, In Add/Edit Attribute dialogbox, select Add Multiple (Bound) and check on Columns.
    SSIS ExportXML File Task - Layout Editor Add Attribute
  5. Now, Right Click on Mappings from left panel of Export XML File task, select Add Element(s) (Below this node). And than, In Add/Edit Element dialogbox, select Add Multiple (Bound) and check on Columns.
    SSIS ExportXML File Task - Layout Editor Add Elements
  6. Here, Right Click on Mappings from left panel of Export XML File task, select Add Document Array (Below this node). And than, configure Add/Edit Element (Array of rows) like below image.
    SSIS ExportXML File Task - Layout Editor Add Document Array
  7. Now, Right Click on Orders from left panel of Export XML File task, select Add Element(s) (Below this node). And than, In Add/Edit Attribute dialogbox for Select Add Multiple (Bound) and check on Columns.
    SSIS ExportXML File Task - Layout Editor Add Document Array Elements
  8. Click OK to save Export XML File Task setting UI.
  9. Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
    SSIS ExportXML File Task - Select Target Mode, Path and Options
  10. Once you are done, Just Execute SSIS Export XML File Task.
    SSIS Export XML File Task - Execute Custom Layout

How to extract data from SQL Server Table to XML file using Export XML File Task (Default Layout).

  1. Double click on ZS Export XML Task for Configure it.
  2. Let's, Generate XML files for selected tables/views. Set Source Connection we have created, click on Select/Edit for Select Source Table/Queries you want to export.
    SSIS ExportXML File Task - Select Source Tables, Queries, Connection
  3. Now, set Target Path and Other Options, Select Target Location from Browse Button. Check on Overwrite target file(s) if you want it. You can Check on Compress file to *.gz format.
    SSIS ExportXML File Task - Select Target Mode, Path and Options
  4. Click OK to save Export XML File Task setting UI.
  5. Once you are done, Just Execute SSIS Export XML File Task.
    SSIS Export XML File Task - Execute Default Layout

How to extract data with Split and Group By column name using Export XML File Task.

  1. Double click on ZS Export XML Task for Configure it.
  2. In the Split Options Tab, check on Enable Split By Size/Rows and specify Group By Column then new file will be created for each unique value of Group By Column (e.g. If you specify Group By Column = Country then one file will be created for each Country).
    SSIS ExportXML File Task - Split file options (Split by Size, Row count and Group by column
  3. Click OK to save Export XML File Task setting UI.

How to extract data with Pretty Print using Export XML File Task.

  1. Double click on ZS Export XML Task for Configure it.
  2. In the XML Options tab and change following setting, Check on Indent Output for Pretty print. You can change Date/Time output format as per your required. There is Option for change File Encoding if you want. even you can change Root Element and Row Element name or you can leave it for defaults name.
    SSIS ExportXML File Task - Select XML Format Options
  3. Click OK to save Export XML File Task setting UI.

Properties

Property Name Description
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.
RootElementName Document root node (First node after XML declaration)
RowElementName Row element name for each data row. This is not applicable if you use custom layout.
Namespaces Prefix and namespaces you would like to use for your XML attributes/elements. You can enter multiple prefix and namespace url using key value pair (separated by comma). Example: ns1=http://abc.com/schema1,ns2=http://abc.com/schema2
Prefix Prefix for Root element. If you don't use Custom Layout then this prefix will be applied to all elements. For example if you want to output element like <ns1:Root> then set prefix ns1 here.
IgnoreStaticAttributeForEmptyNestedPropertyCheck Ignore static attribute from nested element check when [Do not output empty nested elements] is checked
HasMapSupport
FileFormat

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Unknown [0] Unknown
Csv [1] Csv
Xml [2] Xml
Json [3] Json
Excel [4] Excel
Pdf [5] Pdf
Html [6] Html
DoNotOutputNullProperty Do not output property if value is NULL. By default NULL value also output in file (e.g. {"Email": null} --OR-- <email xsi:nil="true"></email>)
DoNotOutputEmptyNestedProperty 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.
SingleDatasetArray Produce document with array from main dataset without need for additional dataset join
IndentOutput Indent JSON output
CharacterSet Character set for generated output. If you set this property then Encoding property is ignored. If you need to export data with encoding without BOM (preamble) then set this property to blank and use Encoding property (It has option to pick with BOM or without BOM).
Encoding File encoding Type (e.g. ASCII, UTF8...)

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
InnerObject
DateFormat Date/time format (e.g. yyyy-MM-dd HH:mm:ss.fff)
ProgressIntervalRows Specify after how may rows you want to report progress in log
Timeout Command Timeout for source query. 0 means no timeout
ThrowErrorIfSourceDataMissing When this setting is true then Source data must have one or more records otherwise task will fail with error.
Connection Connection for source table/query from which you want to export data. This can be valid ADO.net or OLEDB connection Manager Name or ID
Source Source for the data. This can be table(s), query, view(s) or proc. If its other than table or view then you must specify --proc or --query parameter to indicate access mode. You may list multiple objects by separating them using vertical bar (e.g table1|table2|view1). You can also use pattern for objects (e.g.  cust%|prod%)
SourceSplitChar Separator to split multiple source objects / sql queries. If certain characters found in your sql query (e.g. | ) then use this property to specify custom split character.
Schema Schema name or pattern to search for objects when object name contains pattern (e.g. Cust%)
ExcludeViews If pattern used on object name then do not include matching views in the objects to export.
ExcludeTables If pattern used on object name then do not include matching views in the objects to export.
UseMappingFile Use custom layout and additional processing instructions per column. This option is only valid for JSON Export at this moment.
MappingAccessMode AccessMode for Mapping file. This determines how mapping information will be read at runtime

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
DirectValue [0] Direct value
ValueFromVariable [1] Direct value from variable
DirectPath [2] File path or web URL
PathFromVariable [3] File path or web URL from variable
MappingDirectValue Mapping information in the format of XML
MappingVariable Variable which holds mapping information
MappingFilePathVariable Variable which holds mapping file path
MappingFilePath Mapping file path which contains custom layout and additional information
Target Target file or folder path
TargetAccessMode AccessMode for response content write

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Direct [0] Direct
Variable [1] Variable
Connection [2] Connection
TargetVariable Variable in which you want to save response content
TargetConnection Target file connection where you want to write response content
Append Append data at the end of file
Overwrite Overwrite target file if exists
GroupByColumn Split files by group by column value. e.g. if you group by Country field then you will have one file for each country. Country name will be used as file name suffix. If you specify --split option then you may have more than one file for each value in group by field depending on split size/rows you specify
Compress Compress file in gzip format
KeepRaw Keep source file after compressing it when --gzip option enabled. By default source file is deleted once its compressed.
EnableCustomEscapeSequence Enable this option to define custom replacement rules (See also CustomEscapeSequenceRule). Replacement rule should contain key=value pairs separated by semicolon. You can hard code character directly or use designated keyword (e.g. supported keywords are {doublequote} {semicolon} {tab}, {cr}, {lf}, {null}). You can also use Hex chars to search (e.g. \xff44=something). Use blank value in the second part to replace with blank. Example: {null}=\0;"="";{semicolon}=\{semicolon};{cr}=\r;{lf}=\n;{tab}=\t;{columndelimiter}=\{columndelimiter};\xff32=;\xff31=;{null}=;
CustomEscapeSequenceRule Define custom replacement rules to search and replace certain characters. This option is only valid when EnableCustomEscapeSequence=true. Replacement rule should contain key=value pairs separated by semicolon (i.e. ; ). In the key=value pairs, left side you have to define character to be searched and on the right side replacement value. You can hard code character directly or use designated keyword (e.g. supported keywords are {doublequote} {semicolon} {tab}, {cr}, {lf}, {null}). You can also use Hex chars to search (e.g. \xff44=something). Use blank value in the second part to replace with blank. Example: {null}=\0;"="";{semicolon}=\{semicolon};{cr}=\r;{lf}=\n;{tab}=\t;{columndelimiter}=\{columndelimiter};\xff32=;\xff31=;{null}=;
SplitFiles Split into multiple files (when split enabled you can split by filesize or rowcount. Check --splitsize and --splitrows options).
MaxSizePerFile Maximum size per file. You can specify in bytes,kb,mb or gb. Example: 10mb, 5000kb, 1gb, 2000bytes
MaxRowsPerFile Maximum rows per file
MaxRowsToExport Maximum number of rows to export.
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.
AlwaysQuoteRegardlessDataType By default Boolean and numeric values are not placed around quotes (when quoted value setting enabled). If you want to change this behavior set this option to true
LoggingMode

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.

Setting UI

SSIS Export to XML File Task - Setting UI
SSIS Export to XML File Task - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS XML Export Task] category
SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip)

SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip)

Introduction In this blog post you will see how easy it is to load large amount of data from SQL Server to FTP/SFTP. SSIS Data Load SQL Server to FTP/SFTP (Split Files, GZip). For demo purpose we will use SQL Server as relational source but you can use same steps for any database engine […]

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

Copyrights reserved. ZappySys LLC.