For Extensible Source Connections the Browse Connection action is performed by a Scripted Browse Workflow. The Browse Script executed for this action is selected through the Extensible Source Connection’s Properties under the Connection Browse Script setting.
Browse Scripts are created like any other script in the RED metadata with the following restrictions:
- Only Windows-based scripts are supported by the Scripted Browse Workflow.
- The Scripted Browse Workflow expects two lines of output:
The first line is the success code: 1,-1, or -2
The second line is the entire minimized JSON output (line feeds must be removed)
The output JSON must conform to a predefined structure
The predefined structure for the output JSON is best demonstrated by creating the example for an Extensible Source Connection set and performing the ‘Browse Action’.
The example Browse Script saves the output JSON to the user’s temp directory as ouput.json for review. This output should be reviewed together with the Output JSON Mapping section to understand how the structure relates to the UI.
The example Browse Script can be created by adding a new PowerShell (64-bit) script object to RED and selecting Tools->Create Example Browse Script from the empty script editor window. This example is intended to be used in conjunction with the matching set of UI Configuration examples.
Browse Script Output JSON Attribute Mappings
This table shows the expected attribute structure for the Output JSON produced by Browse Scripts and the mappings of those attributes to the RED UI.
| Node/Attribute | Value Type | Description |
|---|---|---|
| .."treeViewLayout" | STRING := "Tabular" | Layout mode of the Browser Tree View |
| .."treeViewIcons" | Node containing the Browser Tree View icon settings Icon Configuration for the Source Browser Tree | |
| ..|.."schema" | STRING | Icon file name for the Schema Node in the Browser Tree View |
| ..|.."table" | STRING | Icon file name for the Table Node in the Browser Tree View |
| .."<Schema Node Label>" | Set <Schema Node Label> as STRING | Displayed in the Browser Tree View as Schema/Folder node containing an array of Objects |
| .."<Table Node Label>" | Set <Table Node Label> as STRING | Displayed in the Browser Tree View as an Object node containing an Object |
| .."treeViewCategory" | STRING | [Optional] The category key to match the treeViewIcons configuration for this node Icon Configuration for the Source Browser Tree |
| ..|.."name" | STRING | The name of the source object used to create the Load Table Name in RED |
| ..|.."description" | STRING | Maps to Description field in Load Table Properties tab in RED |
| ..|.."rowCount" | NUMERIC | Displayed in the Browser Tree View as the object's row count or size. |
| ..|.."columns" | Node containing the array of one or more columns for the object. | |
| ..|..|.."name" | STRING | Maps to Column Name in the Column's properties in RED |
| ..|..|.."treeViewCategory" | STRING | [Optional] The category key name to match to treeViewIcons configuration for this node Icon Configuration for the Source Browser Tree |
| ..|..|.."dataType" | STRING | Data type used in RED Data Type Mapping |
| ..|..|.."dataTypeLength" | NUMERIC|NULL | Data type length used in RED Data Type Mapping |
| ..|..|.."dataTypeScale" | NUMERIC|NULL | Data type scale used in RED Data Type Mapping |
| ..|..|.."dataTypePrecision" | NUMERIC|NULL | Data type precision used in RED Data Type Mapping |
| ..|..|.."nullAllowed" | BOOLEAN := TRUE|FALSE | Maps to the Null Values Allowed field in the Column's properties in RED |
| ..|..|.."defaultValue" | STRING | Maps to the Default Value in the Column's properties in RED |
| ..|..|.."description" | STRING | Maps to the Column Description in the Column's properties in RED |
| ..|..|.."displayName" | STRING | Maps to the Business Display Name in the Column's properties in RED |
| ..|..|.."format" | STRING | Maps to the Format field in the Column's properties in RED |
| ..|..|.."additive" | BOOLEAN := TRUE|FALSE | Maps to the Additive field in the Column's properties in RED |
| ..|..|.."numeric" | BOOLEAN := TRUE|FALSE | Maps to the Format field in the Column's properties in RED |
| ..|..|.."attribute" | BOOLEAN := TRUE|FALSE | Maps to the Attribute field in the Column's properties in RED |
| ..|..|.."sourceTable" | STRING | Maps to the Source Table field in the Column's properties in RED |
| ..|..|.."sourceColumn" | STRING | Maps to the Source Column field in the Column's properties in RED |
| ..|..|.."transform" | STRING | Maps to the Transformation field in the Column's properties in RED |
| ..|..|.."transformType" | STRING := "A"|"D" | Maps to the Transformation Type field in the Column's properties in RED. Only 'A' for After-Load or 'D' for During-load are valid entries. Defaults to During-load when no value is given. |
| ..|..|.."uiConfigColumnProperties" | Node containing the array (if any) of field value pairs to map to any Configured Column UI fields. | |
| ..|..|..|."a_configured_column_UI_field" | STRING|NUMERIC | Maps to "a_configured_column_UI_field" field in the Column's Properties in RED |
| ..|.."loadInfo" | Node containing the object level mappings to the Load Properties Source tab in RED. | |
| ..|..|.."fileLoaderOptions" | STRING | Maps to Loader Options field in Load Table Properties Source tab in RED |
| ..|..|.."fileParsed" | BOOLEAN := TRUE|FALSE | Maps to an internal metadata flag for the Load Table in RED, not currently displayed in the UI |
| ..|..|.."overrideLoadSQL" | STRING | Maps to Loader Options field in Load Table Properties Source tab in RED |
| ..|..|.."overrideSourceColumns" | STRING | Maps to Override Source Columns field in Load Table Properties Source tab in RED |
| ..|..|.."selectDistinctValues" | BOOLEAN := TRUE|FALSE | Maps to Select Distinct Values field in Load Table Properties Source tab in RED |
| ..|..|.."sourceFile" | Node containing the Source File Details | |
| ..|..|..|.."charSet" | STRING | Maps to an internal metadata field for the Load Table in RED, not currently displayed in the UI |
| ..|..|..|.."escapeEncoding" | STRING | Maps to an internal metadata field for the Load Table in RED, not currently displayed in the UI |
| ..|..|..|.."fieldDelimiter" | STRING | Maps to Source File Field Delimiter field in Load Table Properties Source tab in RED |
| ..|..|..|.."fieldEnclosure" | STRING | Maps to Source File Field Enclosure Delimiter field in Load Table Properties Source tab in RED |
| ..|..|..|.."headerLine" | STRING | Maps to Source File has Headings/Labels field in Load Table Properties Source tab in RED |
| ..|..|..|.."name" | STRING | Maps to Source File Name field in Load Table Properties Source tab in RED |
| ..|..|..|.."nonStringNullEncoding" | STRING | Maps to Loader Options field in Load Table Properties Source tab in RED |
| ..|..|..|.."nullEncoding" | STRING | Maps to Loader Options field in Load Table Properties Source tab in RED |
| ..|..|..|.."path" | STRING | Maps to Source File path field in Load Table Properties Source tab in RED |
| ..|..|..|.."recordDelimiter" | STRING | Maps to Source File Record Terminator field in Load Table Properties Source tab in RED |
| ..|..|.."sourceSchema" | STRING | Maps to Source Schema field in Load Table Properties Source tab in RED |
| ..|..|.."sourceTables" | STRING | Maps to Source Table(s) field in Load Table Properties Source tab in RED |
| ..|..|.."useOverrideSourceColumns" | BOOLEAN := TRUE|FALSE | Maps to Override Source Column/Transformations field in Load Table Properties Source tab in RED |
| ..|..|.."whereAndGroupByClauses" | STRING | Maps to Where and Group By Clauses field in Load Table Properties Source tab in RED |
| ..|.."uiConfigLoadTableProperties" | Node containing the array (if any) of field value pairs to map to any Configured Load Table UI fields. | |
| ..|..|.."a_configured_load_table_UI_field" | STRING|NUMERIC | Maps to "a_configured_load_table_UI_field" field in Load Table Properties Source tab in RED |
Scripted Browse Workflow
The Scripted Browse Workflow performs the following steps when browsing an Extensible Source Connection:
- Prepares the Browse Script and run-time files for execution against the associated Windows Script Connection.
- Executes the Script and if a success code of 1 is returned:
- Read the second line of output.
- Check if the output is valid JSON.
- Check if the output has the expected attribute structure.
- Read the structure into the Source Browser Tree to create Load Tables from the items displayed.
- Delete all temporary files created during the Scripted Browse Workflow.
- Or - if the script returns a failure code (-1,-2) then the temporary files are preserved for debugging and the output is not loaded into the Source Browser Tree.
Any object displayed in the Source Browser Tree can be dragged into RED as a Load Table Object as normal and any valid metadata set in the output JSON structure is carried across to RED for that object.
Icon Configuration for the Source Browser Tree
The configuration of the Source Browse Tree icons for Extensible Source Connections helps to distinguish and identify categories more easily.
The treeViewCategory is an optional attribute that can appear at the Table and Column node level. Each Table or Column can have an association with a treeViewCategory that will be matched with the treeViewIcon category key and will display the icon you want to use to identify the node.
Define "category key":"icon name" pairs in the treeViewIcons attributes, where ‘category key’ is an arbitrary string to act as a key for your category and ‘icon name’ is either the file name of the icon file to use without path (assumes the icon is located in the installation folder ‘Icons’ directory) or the full file path to the icon file. If there is an issue loading the icon for a particular node RED will fall back to the default icons without reporting an error.
The image below shows an example of customized tree view icons followed by the output JSON structure that was used to create it:
$outputJson = @"
{
"treeViewLayout": "Tabular",
"treeViewIcons": {
"schema": "Database.ico",
"table": "table.ico",
"view": "view.ico",
"object": "DataTypeObject.ico",
"boolean": "DataTypeBoolean.ico"
},
"my_Schema": {
"my_View": {
"treeViewCategory": "view",
"name": "myView",
"description": "description of table: myView",
"rowCount": 0,
"columns": [{
"treeViewCategory": "object",
"name": "json_column",
"dataType": "json",
"dataTypeLength": null,
"dataTypeScale": null,
"dataTypePrecision": null,
"nullAllowed": true,
"defaultValue": "json_column",
"description": "description of json_column",
"displayName": "display as json_column",
"format": "",
"additive": false,
"numeric": false,
"attribute": true,
"sourceTable": "Icons",
"sourceColumn": "json_column",
"transform": "",
"transformType": "",
"uiConfigColumnProperties": {}
}
],
"loadInfo": {
"fileLoaderOptions": "",
"fileParsed": false,
"overrideLoadSQL": "",
"overrideSourceColumns": "",
"selectDistinctValues": false,
"sourceFile": {
"charSet": "",
"escapeEncoding": "",
"fieldDelimiter": ",",
"fieldEnclosure": "\"",
"headerLine": true,
"name": "",
"nonStringNullEncoding": "",
"nullEncoding": "",
"path": "",
"recordDelimiter": ""
},
"sourceSchema": "mySourceSchema",
"sourceTables": "mySourceTables",
"useOverrideSourceColumns": false,
"whereAndGroupByClauses": "Where 1=1"
},
"uiConfigLoadTableProperties": {}
},
"my_Table": {
"treeViewCategory": "table",
"name": "myTable",
"description": "description of table: myTable",
"rowCount": 0,
"columns": [{
"treeViewCategory": "boolean",
"name": "true_or_false_column",
"dataType": "bool",
"dataTypeLength": null,
"dataTypeScale": null,
"dataTypePrecision": null,
"nullAllowed": true,
"defaultValue": "true_or_false_column",
"description": "description of true_or_false_column",
"displayName": "display as true_or_false_column",
"format": "",
"additive": false,
"numeric": false,
"attribute": true,
"sourceTable": "Icons",
"sourceColumn": "true_or_false_column.ico",
"transform": "",
"transformType": "",
"uiConfigColumnProperties": {}
}
],
"loadInfo": {
"fileLoaderOptions": "",
"fileParsed": false,
"overrideLoadSQL": "",
"overrideSourceColumns": "",
"selectDistinctValues": false,
"sourceFile": {
"charSet": "",
"escapeEncoding": "",
"fieldDelimiter": ",",
"fieldEnclosure": "\"",
"headerLine": true,
"name": "",
"nonStringNullEncoding": "",
"nullEncoding": "",
"path": "",
"recordDelimiter": ""
},
"sourceSchema": "mySourceSchema",
"sourceTables": "mySourceTables",
"useOverrideSourceColumns": false,
"whereAndGroupByClauses": "Where 1=1"
},
"uiConfigLoadTableProperties": {}
}
}
}
"@
Write-Output 1
# *IMPORTANT* remove line endings so that the output is printed in a single line
# This avoids "Invalid JSON" error as the recieving process only consumes the
# second line of the standard-out as its entire input argument.
Write-Output $($outputJson -replace '\r\n', '')
# For debugging the JSON has been output to the user's temp directory
Out-File -InputObject $outputJson -FilePath $env:Temp"\output_test.json"