Connection String Attributes
PreviousNext

Introduction

This page lists all possible attributes you can use in ConnectionString or WITH clause of SQL Query. If you are not sure how to write correct connection string then perform following steps.
  1. Search for odbcad32 in the start menu. Click to launch it.
  2. Configure desired properties in the grid and check Query Preview to make sure everything is correct.
  3. Click on [Copy Connection String] button to generate full connection string.

Connection String Examples

Syntax: (Direct Connection string without DSN)
Driver={ZappySys SFTP XML Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;] 
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]

Connection String Attributes

AttributeLabelDescription
AccessMode
(Setting of XmlConfigurationParams)
AccessModeDefines how to read data from file path/URL or direct string

Available Options

Option Description
DirectPath DirectPath
DirectValue DirectValue
ConnectionStringFile
(Setting of XmlConfigurationParams)
ConnectionStringFile
ConvertFormat
(Setting of XmlConfigurationParams)
ConvertFormatOutput convert raw XML document to JSON (Recommended). This option is ignored if OutputRawDocument=false. Once you do that any further parsing downstream must use JSON Parser rather than XML Parser.
Data
(Setting of XmlConfigurationParams)
DirectValueDirect String you like to use as a datasource. This attribute always takes precedence if both Data and Src defined (i.e. File/Url). You can also use AccessMode=1 along with Data='...' in WITH clause to use Direct String rather than URL/File.
EnableCustomReplace
(Setting of XmlConfigurationParams)
EnableCustomReplaceEnables custom search / replace in the document text after its read from the file/url or direct string. This replace operation happens before its parsed. This option can be useful for custom escape sequence in source document which is causing issue in the parser. You can replace such unwanted characters fore parser starts parsing the text.
IgnoreContentLengthHeaderForGzipResponse
(Setting of XmlConfigurationParams)
IgnoreContentLengthHeaderForGzipResponse
ReplaceWith
(Setting of XmlConfigurationParams)
ReplaceWithString you like to replace with (Only valid when EnableCustomReplace option is turned on). If you added --regex or --regex-ic at the end of your SearchFor string then ReplaceWith can use special placeholders (i.e. $1, $2...) based on regular expression groups. For example you SearchFor=(\w+)(@\w+.com) to search for emails then to mask emails you can something like this for ReplaceWith = ****$2 (where $2 is domain part and $1 is before @)
SearchFor
(Setting of XmlConfigurationParams)
SearchForString you like to search for (Only valid when EnableCustomReplace option is turned on). If you want to enable Regular Expression pattern search then add --regex or --regex-ic (for case-insensitive search)  at the end of your search string (e.g. ORDER-\d+--regex OR ORDER-\d+--regex-ic (case-insensitive search) )
2D Array Settings
ArrayTransColumnNameFilter
(Setting of XmlConfigurationParams)
Array Transformation ColumnName FilterFilter expression to use to extract column names for array transformation.
ArrayTransCustomColumns
(Setting of XmlConfigurationParams)
Columns Names for Array TransformationWhen you have 2D array but don't have column list specified in a separate array then use specify column names here. Use comma separated list (e.g col1,col2,col3 ). Column name Order must match value order.
ArrayTransEnableCustomColumns
(Setting of XmlConfigurationParams)
Use Custom Columns for Array TransformationWhen you have 2D array but don't have column list specified in a separate array then use this option (e.g. { arr: [[10,11],[21,22]] } ). If you selected Column less array or JSON Lines option then this property means Column Names coming from First Line of array.
ArrayTransformType
(Setting of XmlConfigurationParams)
Array Transformation TypeArray Transformation you want to apply. Useful for case when you have 2-Dimensional arrays with rows/columns in separate arrays.

Available Options

Option Description
None None
TransformSimpleTwoDimensionalArray Simple 2-dimensional array (e.g. {cols:[..], rows:[[..],[..]]} )
TransformComplexTwoDimensionalArray Complex 2-dimensional array  (e.g. {cols:[{..},{..}], rows:[{f:[..]},{f:[..]}] )
TransformKeyValuePivot Key/Value to Columns
TransformMultipleColumnsExpressions Multiple columns using expressions
TransformColumnslessArray Columnless array (e.g. [[..],[..]] )
TransformJsonLineArray JSON Lines - Single Dimension Array(s) (i.e. [..][..] )
TransformPivotColumnlessArray Pivot - Columnless array (e.g. [..] )
ArrayTransRowValueFilter
(Setting of XmlConfigurationParams)
Array Transformation Row FilterFilter expression for row values (Not applicable for simple array transformation).
Array Handling
ElementsToTreatAsArray
(Setting of XmlConfigurationParams)
Element names to treat as arrayComma separated element names which will be treated as Array regardless how many times element repeats at the same level. By default only those elements are treated as array if element appears more than once at the same level.
Cache Settings
CacheEntryTtl
(Setting of XmlConfigurationParams)
Cache entry lifetime, secThis property indicates how many seconds data is kept in cache (in case caching is enabled)
CacheFileLocation
(Setting of XmlConfigurationParams)
Cache file locationThis property allows you to set cache file location (you have to set Cache persistence mode to Storage)
CacheStorage
(Setting of XmlConfigurationParams)
Cache storageSpecifies cache storage - in-memory only or local data file

Available Options

Option Description
Memory Memory
File File
CachingMode
(Setting of XmlConfigurationParams)
Cache modeEnables or disables cache usage for metadata / data

Available Options

Option Description
Disabled Caching disabled
Metadata Cache metadata
All Cache all
Encoding / Culture Settings
CharacterSet
(Setting of XmlConfigurationParams)
CharacterSetCharacter set for text (e.g. utf-8 )
Culture
(Setting of XmlConfigurationParams)
CultureCulture code (e.g. pt-BT). This helps to parse culture specific number formats (e.g. In some culture you may have comma rather than decimal points 0.1 can be 0,1)
Encoding
(Setting of XmlConfigurationParams)
EncodingEncoding of source file

Available Options

Option Description
Default Default
ASCII ASCII
UTF8 UTF-8
UTF16 UTF-16 LE (i.e. Unicode Little Endian)
UTF32 UTF-32
UTF8WithoutBOM UTF-8 Without BOM
UTF32WithoutBOM UTF-32 Without BOM
UTF7 UTF-7
UTF7WithoutBOM UTF-7 Without BOM
UTF16WithoutBOM UTF-16 Without BOM
BigEndian UTF-16 BE (i.e. Unicode Big Endian)
BigEndianWithoutBOM UTF-16 BE Without BOM
Error Handling Settings
ConvErrorHandlingMode
(Setting of XmlConfigurationParams)
Error handling for data conversionDetermines whether some data conversion errors are ignored or stop query processing.

Available Options

Option Description
ThrowError Stop execution on data conversion error
Ignore Ignore data conversion errors
Filter Settings
ElementScopeSeparator
(Setting of XmlConfigurationParams)
Child scope separatorScope separator for nested properties (Child properties under selected filter). For example you may have $.contacts[*] as your filter and for each contact you may have nested node e.g AddressInfo / ZipInfo / Zip. By default column name will be AddressInfo.ZipInfo.Zip (Default separator is dot) but if you want column name like AddressInfo_ZipInfo_Zip in the output then change this property to any custom value (e.g. _). If you dont set this property then dot will be used.
FilterForParentColumnsWithArrayType
(Setting of XmlConfigurationParams)
Filter for parent columns of array typeSet this option to true if you want to output parent columns which are of array type. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property
IncludeParentColumns
(Setting of XmlConfigurationParams)
Include parent columnsIf this option is enabled then parent properties of non-array type will be output along with regular row attributes.
IncludeParentColumnsWithArrayType
(Setting of XmlConfigurationParams)
Include parent columns of array typeSet this option to true if you want to output parent columns which are array. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property
IncludeParentWhenChildNull
(Setting of XmlConfigurationParams)
IncludeParentWhenChildNullBy default child and parent information is not included in the output if children not found for specified expression. For example if you want to extract all orders from all customers nodes then you can type $.Customers[*].Orders[*]. This will fetch all orders from all customers. By default customers records without orders wont be included in the output. If you want to include those customers where orders not found then check this option (Output null information for order attributes). This behavior is similar to LEFT OUTER JOIN in SQL (Left side is parent, right side is child). This option is ***resource intensive*** so only check if you really care about this behavior.
LevelSeparator
(Setting of XmlConfigurationParams)
Parent scope separatorProperty level separator used in generated property name (separator for outer properties - Above selected filter node). Use this if default separator is producing duplicate property name which is conflicting with existing name.
LevelsToScan
(Setting of XmlConfigurationParams)
Maximum levels to scanSpecifies how many nested levels should be scanned. 0 means no limitations to scan.
ParentColumnPrefix
(Setting of XmlConfigurationParams)
Parent prefixValue of this parameter will be used as prefix of parent element. Empty means default prefix will be used.
ThrowErrorWhenNoMatch
(Setting of XmlConfigurationParams)
Throw error on Filter no-matchesDetermines whether error is thrown in case data table name element is missing from response or no data row is returned.
Log Settings
LoggingLevel
(Setting of XmlConfigurationParams)
Log levelSets level of messages logged

Available Options

Option Description
None None
Error Error
Debug Debug
Info Info
LogPath
(Setting of XmlConfigurationParams)
Log file locationSets location for the log file (empty value means no log written)
LogSize
(Setting of XmlConfigurationParams)
Maximum log file size (Mb)Sets limit for log file size (0 means no limit, old log entries will be discarded if size limit is reached)
GroupName
(Setting of ColumnsMetadata)
Group name
OverrideMode
(Setting of ColumnsMetadata)
OverrideMode
Metadata Settings
MetaScanMode
(Setting of XmlConfigurationParams)
Metadata Scan Mode / MultiplierMetadata guess mode / multiplier to detect length by scanning rows defined in

Available Options

Option Description
Auto Auto
Strict Strict - Exact length
Guess2x Guess2x - 2 times bigger
Guess3x Guess3x - 3 times bigger
Guess4x Guess4x - 4 times bigger
TreatAsUnicodeString Set all columns as string
Guess10x Guess10x - 10 times bigger
TreatStringAsMaxLength Set string columns with MAX Length - i.e. DT_WSTR(4000)
TreatStringAsBlob Set string columns as BLOB - i.e. DT_NTEXT
MetaTreatStringAsAscii
(Setting of XmlConfigurationParams)
Treat string as ASCIIWhen this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)
RowsToScanForMetadata
(Setting of XmlConfigurationParams)
Number of rows scanned to collect metadataIf metadata isn't defined for query the driver will try to scan some rows to guess fields parameters
TreatBlankBoolAsNull
(Setting of XmlConfigurationParams)
Treat blank BOOLEAN value as NULL
TreatBlankDateAsNull
(Setting of XmlConfigurationParams)
Treat blank DATE/TIME value as NULL
TreatBlankNumberAsNull
(Setting of XmlConfigurationParams)
Treat blank NUMERIC value as NULL
Other Settings
DateParseHandling
(Setting of XmlConfigurationParams)
Date data typeSpecifies how dates are parsed.

Available Options

Option Description
None Keep date as string
DateTime Convert to DateTime (Timezone lost)
DateTimeOffset Convert to DateTimeOffset (Preserve Time zone)
EnableBigNumberHandling
(Setting of XmlConfigurationParams)
Enable Big Number HandlingWhen this option is checked, sytem allows to parse large number (Higher than Int64) in JSON document (i.e. { id: 76000009353950433247780 } ). If you do not turn on this option then it may throw error if large number is encountered. This option is SLOW becuase it has to perform additional checks in order to avoid Large number overflow error so do not turn on unless you getting Large number error.
EnablePerformanceMode
(Setting of XmlConfigurationParams)
Enable Stream ModeEnables memory optimized mode. You may lose certain functionality when you turn on this. Only turn on this feature if you getting out of memory error.
FloatParseHandling
(Setting of XmlConfigurationParams)
FloatParseHandlingSpecifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.

Available Options

Option Description
Double Default (Double [~15-17 digits])
Decimal Decimal (High Precision / Scale [~28-29 digits] )
Recursive
(Setting of XmlConfigurationParams)
Scan files recursivelyScan files recursively when you read from local  files (i.e. include files from sub folders).
Output Settings
DateFormatString
(Setting of XmlConfigurationParams)
Date formatSpecifies date format
DefaultRowLimit
(Setting of XmlConfigurationParams)
Default data row limit (i.e. LIMIT / TOP clause)Use this option if you like to limit number of rows fetched by any query. If you use LIMIT clause in the query then this option is ignored.
EnableArrayFlattening
(Setting of XmlConfigurationParams)
Enable Array FlatteningEnables deep array flattening for selected filtered hierarchy. When you turn on this property it will flatten each property of each array item and expose as column (e.g. If you have Filter set as $.customers[*] and for each customer you have an array of Addresses then you may see output columns like Addresses.1.City, Addresses.1.State, Addresses.2.City, Addresses.2.State .... Addresses.N.City, Addresses.N.State). You can control how many array items you want to flatten by setting MaxArrayItemsToFlatten property.
EnablePivot
(Setting of XmlConfigurationParams)
Enable PivotWhen this property is true then Column is converted to Row. Pivoted names will appear under  Pivot_Name column and values will appear under Pivot_Value field.
EnableRawOutputMode
(Setting of XmlConfigurationParams)
Enable Raw Document Output ModeOutput as raw JSON/XML document rather than parsing individual fields. This option is helpful if you have documents stored in a file and you want to pass them downstream as raw JSON string rather than parsing into columns.
EnableRawOutputModeSingleRow
(Setting of XmlConfigurationParams)
Enable Raw Document - Single Row (Disable parser, allow any format)Enable Raw Document Output Mode for any format (i.e. XML, Html, Text, Json). Unlike other option EnableRawOutputMode, this option doesn't invoke parser to extract documents by finding row terminator. It will source string as row value in single row/ single column.
ExcludeColumns
(Setting of XmlConfigurationParams)
Excluded propertiesList of comma separated element names which you want to exclude from output. Specify parent element name to exclude all child nodes.
FileCompressionType
(Setting of XmlConfigurationParams)
FileCompressionTypeCompression format for source file (e.g. gzip, zip)

Available Options

Option Description
None None
GZip GZip
Zip Zip
TarGZip TarGZip
IndentOutput
(Setting of XmlConfigurationParams)
Indent outputIndent JSON output to improve readability.
MaxArrayItemsToFlatten
(Setting of XmlConfigurationParams)
Max Array Items To FlattenMaximum number of array items to flatten. inner array flattening. Adjust this property to control how many columns being generated. This option is ignored if you set EnableArrayFlattening=false
RawOutputDataRowTemplate
(Setting of XmlConfigurationParams)
RawOutputDataRowTemplate
RawOutputExtractMode
(Setting of XmlConfigurationParams)
RawOutputExtractMode

Available Options

Option Description
None None
Json Json
Xml Xml
Regex Regex
RawOutputFilterExpr
(Setting of XmlConfigurationParams)
RawOutputFilterExprUse this property to define one or multiple expressions (i.e. JsonPath,XPath,Regex) to extract information from source. This property is only valid when EnableRawOutputModeSingleRow=true and RawOutputDataRowTemplate is set.
SaveContentAsBinary
(Setting of XmlConfigurationParams)
SaveContentAsBinary
Query Engine Temp Storage
TempStorageMode
(Setting of XmlConfigurationParams)
Intermediate results storageUse [Disk] if number of resulting rows or row data size is large

Available Options

Option Description
Memory Memory
Disk Disk
Request Settings
DataFormat
(Setting of XmlConfigurationParams)
Data formatData format coming from HTTP Response. This is useful for example when you have OData service and you want to automatically consume all pages of data using odata.nextUrl. Setting DataFormat=Odata will automatically do it for you.

Available Options

Option Description
Notset Default
Original Original
Odata OData
DataPath
(Setting of XmlConfigurationParams)
Data Source (URL or File Path)Data source URL or File Path (e.g. http://abc.com/api/get --or-- c:\data\cust01.json  --or-- c:\data\cust*.json ). If data source is file then wildcard pattern can be also used to read multiple files (e.g. c:\data\cust*.json will read all files that match wildcard)
Filter
(Setting of XmlConfigurationParams)
FilterSpecifies expression to filter data. Usually this is Array path from which you like to get all records. Example:  $.Customers[*] will get all customers from Customer Array from your document and $.Customers[*].Orders[*] will get all orders for all customers.


Copyrights reserved. ZappySys LLC.