|
Connection String Attributes
|
 
|
|
Connection String Attributes
|
|
Driver={ZappySys SFTP XML Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
| Attribute | Label | Description | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AccessMode (Setting of XmlConfigurationParams) | AccessMode | Defines how to read data from file path/URL or direct string
| ||||||||||||||||||||||||||
| ConnectionStringFile (Setting of XmlConfigurationParams) | ConnectionStringFile | |||||||||||||||||||||||||||
| ConvertFormat (Setting of XmlConfigurationParams) | ConvertFormat | Output 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) | DirectValue | Direct 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) | EnableCustomReplace | Enables 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) | ReplaceWith | String 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) | SearchFor | String 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 Filter | Filter expression to use to extract column names for array transformation. | ||||||||||||||||||||||||||
| ArrayTransCustomColumns (Setting of XmlConfigurationParams) | Columns Names for Array Transformation | When 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 Transformation | When 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 Type | Array Transformation you want to apply. Useful for case when you have 2-Dimensional arrays with rows/columns in separate arrays.
| ||||||||||||||||||||||||||
| ArrayTransRowValueFilter (Setting of XmlConfigurationParams) | Array Transformation Row Filter | Filter expression for row values (Not applicable for simple array transformation). | ||||||||||||||||||||||||||
| Array Handling | ||||||||||||||||||||||||||||
| ElementsToTreatAsArray (Setting of XmlConfigurationParams) | Element names to treat as array | Comma 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, sec | This property indicates how many seconds data is kept in cache (in case caching is enabled) | ||||||||||||||||||||||||||
| CacheFileLocation (Setting of XmlConfigurationParams) | Cache file location | This property allows you to set cache file location (you have to set Cache persistence mode to Storage) | ||||||||||||||||||||||||||
| CacheStorage (Setting of XmlConfigurationParams) | Cache storage | Specifies cache storage - in-memory only or local data file
| ||||||||||||||||||||||||||
| CachingMode (Setting of XmlConfigurationParams) | Cache mode | Enables or disables cache usage for metadata / data
| ||||||||||||||||||||||||||
| Encoding / Culture Settings | ||||||||||||||||||||||||||||
| CharacterSet (Setting of XmlConfigurationParams) | CharacterSet | Character set for text (e.g. utf-8 ) | ||||||||||||||||||||||||||
| Culture (Setting of XmlConfigurationParams) | Culture | Culture 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) | Encoding | Encoding of source file
| ||||||||||||||||||||||||||
| Error Handling Settings | ||||||||||||||||||||||||||||
| ConvErrorHandlingMode (Setting of XmlConfigurationParams) | Error handling for data conversion | Determines whether some data conversion errors are ignored or stop query processing.
| ||||||||||||||||||||||||||
| Filter Settings | ||||||||||||||||||||||||||||
| ElementScopeSeparator (Setting of XmlConfigurationParams) | Child scope separator | Scope 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 type | Set 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 columns | If 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 type | Set 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) | IncludeParentWhenChildNull | By 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 separator | Property 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 scan | Specifies how many nested levels should be scanned. 0 means no limitations to scan. | ||||||||||||||||||||||||||
| ParentColumnPrefix (Setting of XmlConfigurationParams) | Parent prefix | Value 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-matches | Determines 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 level | Sets level of messages logged
| ||||||||||||||||||||||||||
| LogPath (Setting of XmlConfigurationParams) | Log file location | Sets 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 / Multiplier | Metadata guess mode / multiplier to detect length by scanning rows defined in
| ||||||||||||||||||||||||||
| MetaTreatStringAsAscii (Setting of XmlConfigurationParams) | Treat string as ASCII | When 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 metadata | If 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 type | Specifies how dates are parsed.
| ||||||||||||||||||||||||||
| EnableBigNumberHandling (Setting of XmlConfigurationParams) | Enable Big Number Handling | When 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 Mode | Enables 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) | FloatParseHandling | Specifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.
| ||||||||||||||||||||||||||
| Recursive (Setting of XmlConfigurationParams) | Scan files recursively | Scan files recursively when you read from local files (i.e. include files from sub folders). | ||||||||||||||||||||||||||
| Output Settings | ||||||||||||||||||||||||||||
| DateFormatString (Setting of XmlConfigurationParams) | Date format | Specifies 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 Flattening | Enables 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 Pivot | When 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 Mode | Output 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 properties | List 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) | FileCompressionType | Compression format for source file (e.g. gzip, zip)
| ||||||||||||||||||||||||||
| IndentOutput (Setting of XmlConfigurationParams) | Indent output | Indent JSON output to improve readability. | ||||||||||||||||||||||||||
| MaxArrayItemsToFlatten (Setting of XmlConfigurationParams) | Max Array Items To Flatten | Maximum 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 |
| ||||||||||||||||||||||||||
| RawOutputFilterExpr (Setting of XmlConfigurationParams) | RawOutputFilterExpr | Use 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 storage | Use [Disk] if number of resulting rows or row data size is large
| ||||||||||||||||||||||||||
| Request Settings | ||||||||||||||||||||||||||||
| DataFormat (Setting of XmlConfigurationParams) | Data format | Data 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.
| ||||||||||||||||||||||||||
| 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) | Filter | Specifies 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. | ||||||||||||||||||||||||||