USING THE DATABC FME FRAMEWORK
The DataBC Feature Manipulation Engine (FME) Framework was written to allow the structure of FME scripts used in the DataBC program to be standardized, making it easier to maintain and troubleshoot these scripts.
This page provides instructions on developing a FME Workbench file to DataBC FME Framework standards.
AUDIENCE | ||
---|---|---|
Data Publishers | Data Architects | Vendors |
Table of Contents
- ACCESSING THE FME FRAMEWORK
- CREATING AN FMW
- APPENDIX 1 - NOTIFICATIONS
- APPENDIX 2 - NON-BCGW DESTINATIONS
- APPENDIX 3 - KIRK PREPARATION
- APPENDIX 4 - FINAL PUBLICATION CHECKLIST
ACCESSING THE FME FRAMEWORK
Before using the FME Framework for the first time there are a couple of FME options that have to be reset. These steps should only have to be done once.
Note that FME 2017 was the version used in preparing the following instructions and images.
Allow Reader Feature Type Editing
The FME Framework requires you to be able to link published parameters to reader feature types (tables). By default, FME will not allow you to do this. You must explicitly allow FME to Allow reader feature type editing, by following these steps:
- Open FME Workbench and from the menu bar
- Choose Tools → FME Options
- Select Workbench in the left hand panel of the FME Options popup
- Check the box Allow reader feature type editing in the General Defaults section (or in some FME versions, you’ll need to expand the Reader/Writer Options section to find the check box)
Enable FME Framework Customizations
- Open FME Workbench and on the menu bar
- Choose Tools → FME Options
- Select Default Paths in the left hand panel of the FME Options popup
- Click on the + symbol in the Shared FME Folders section
- Enter this directory: \data.bcgov\work\scripts\python\DataBCFmeTemplate2\fmeCustomizations
- Click YES on the popup to create one or more of the required subfolders
- Click OK
Set the Path to the Python Scripts Underlying the FME Framework
- Open the Windows Command Prompt window (in the Start Menu list under the Windows System folder)
- Check if you have already defined the PYTHONPATH user environment variable
- Type the
set
command at the prompt - Look for a variable listed called PYTHONPATH and if it is set to the correct location
- Type the
- If there is no PYTHONPATH variable, then you’ll create it now
- At the prompt type the following:
setx PYTHONPATH "\\data.bcgov\work\scripts\python\DataBCFmeTemplate2\"
- A SUCCESS message should be displayed
- Note: a new/updated environment variable won’t be visible in the set list until after you exit the Commond Prompt window
- At the prompt type the following:
- Exit the Command Prompt window
CREATING AN FMW
- Start with a new FMW or one of the FME Framework templates.
- Always test your scripts in the BCGW Delivery environment before submitting them to DataBC.
If developing on a GTS server, configure a dbCreds.json file
The DataBC FME Framework, when run on a DataBC FME Workbench workstation or on FME Server, retrieves passwords from a password management system accessible only by DataBC processes. GTS servers do not have access to this password management system, so scripts that are running on the GTS environment need some other way of retrieving passwords. This is done through supplying a file called ‘dbCreds.json’ in the same directory in which the FMW being run by FME Workbench is located. Example files are available here. Copy the example .json file to the same directory as your FMW, then open it up and add the required parameters. Also make sure to copy the associated .sde connection file into this same directory.
When you deliver the FMW to DataBC, do not include the dbCreds.json file with the files you deliver. Instead arrange with DataBC to have the passwords included into our password management system.
Add the Source Reader(s)
FME Readers are added to a workspace using then menu item Readers → Add Readers…
FeatureReader and other “Reading” Transformers
In general, FME scripts use FME Readers to read source dataset records. There are, however, occasions where it is preferable to use a FeatureReader transformer, a SQL Creator transformer, or a SQL Executor transformer instead of an FME Reader. In particular, if the script implements a series of consecutive read-transform-write patterns, with subsequent read-transform-writes being executed depending on the success of earlier ones, then it is preferable to use FeatureReader and FeatureWriter transformers in a single script rather than using traditional Readers and Writers in multiple scripts called by FMEServerJobSubmitter. FeatureReader transformers can be inserted using the menu item Transformers → Add Transformer… → FeatureReader
The following sections describe the properties to enter when adding a new Reader or equivalent transformer:
- Comma Separated Value (CSV)
- Esri SDE Geodatabase (GEODATABASE_SDE)
- Esri File (gdb) Geodatabase (GEODATABASE_FILE)
- Esri Shapefile (SHP)
- GeographicJavaScript Object Notation (GeoJSON)
- Microsoft Access Database (MDB_ADO)
- Microsoft Excel (XLSX, XLS)
- Oracle Non-Spatial
- Oracle Spatial Object (SDO)
Comma Separated Value (CSV)
- Comma Separated Value (.csv) files are a common source of data for the BCGW. They may be spatial (having latitude and longitude columns) or non-spatial. Common locations of source CSV files are:
- The read-only staging area
\\data.bcgov\data_staging_ro\BCGW\...
- The BC Data Catalogue file store:
https://catalogue.data.gov.bc.ca/dataset/<dataset permalink>/resource/<resource permalink>/download/<filename>.csv
Field Value Format CSV (Comma Separated Value) Dataset Select the .csv file to be read.
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...)Parameters normally the defaults are fine - The read-only staging area
Next: Define the Source Reader User Parameters
Esri SDE Geodatabase (GEODATABASE_SDE)
-
Tables and views registered with SDE or the geodatabase in an Oracle SDE-enabled database should be read using the GEODATABASE_SDE reader.
Field Value Format Esri Geodatabase (ArcSDE Geodb) Dataset N/A Parameters specify Connection File, check Override Credentials, specify Username, Password, Table List
Next: Define the Source Reader User Parameters
When adding a GEODATABASE_SDE reader, specify hard-coded values for the parameters listed above; they will be overridden by FME parameters at a later stage in the configuration.
Esri File (gdb) Geodatabase (GEODATABASE_FILE)
- Common locations of source file geodatabases are:
- The read-only staging area
\\data.bcgov\data_staging_ro\BCGW\...
- The BC Data Catalogue file store:
https://catalogue.data.gov.bc.ca/dataset/<dataset permalink>/resource/<resource permalink>/download/<filename>.zip
Field Value Format Esri Geodatabase (File Geodb)
Do not use Esri Geodatabase (File Geodb Open API)Dataset select the .gdb folder.
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...)Parameters Specify the feature types to read. - The read-only staging area
Next: Define the Source Reader User Parameters
Esri Shapefile (SHP)
- Common locations of source shapefiles are:
- The read-only staging area
\\data.bcgov\data_staging_ro\BCGW\...
- The BC Data Catalogue file store:
https://catalogue.data.gov.bc.ca/dataset/<dataset permalink>/resource/<resource permalink>/download/<filename>.zip
Field Value Format Esri Shapefile Dataset Select the .shp file to be read.
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...)Parameters Normally the defaults are fine - The read-only staging area
Next: Define the Source Reader User Parameters
GeographicJavaScript Object Notation (GeoJSON)
- Common locations of source GeoJSON files are:
- Operational systems, through a REST web service (e.g., https://gwells-prod.pathfinder.gov.bc.ca/gwells/api/v1/gis/wells)
- The BC Data Catalogue file store:
https://catalogue.data.gov.bc.ca/dataset/<dataset permalink>/resource/<resource permalink>/download/<filename>.json
Field Value Format GeoJSON (Geographic JavaScript Object Notation) Dataset Select the .json or .geojson file to be read or the URL to a web accessible file Parameters Normally the defaults are fine
Next: Define the Source Reader User Parameters
Microsoft Access Database (MDB_ADO)
-
This reader is used in cases where the operational database management system is Microsoft Access and the database has been copied to the staging area. This is a fairly rare situation.
Field Value Format Microsoft Access Dataset Select the .mdb or .accdb file to be read.
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...)Parameters Enter password, if needed, and select the tables to be read
Next: Define the Source Reader User Parameters
Microsoft Excel (XLSX, XLS)
-
Some source spreadsheet datasets are in XLSX or XLS format rather than the open CSV format. This can be the case where there are other consumers of the spreadsheet who require to see the spreadsheet in the context of other sheets in the workbook. Having FME read from the XLSX/XLS workbook removes the need for the client to save an extra copy a the spreadsheet in CSV.
Field Value Format Microsoft Excel Dataset Select the .xlsx or .xls file to be read.
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...)Parameters Select the sheet(s) to be read, the cell range(s), and columns.
Next: Define the Source Reader User Parameters
Oracle Non-Spatial
-
This reader is used in cases where the input dataset is non-spatial and not registered with SDE or an SDE geodatabase.
Field Value Format Oracle Non-Spatial Dataset Embed Connection Parameters Parameters specify Service Name or Easy Connect, Username, Password, Table List
When adding the reader, specify hard-coded values for the parameters listed above; they will be overridden by FME parameters at a later stage in the configuration.
Next: Define the Source Reader User Parameters
Oracle Spatial Object (SDO)
-
This reader is used in cases where the input dataset is spatial and not registered with SDE or an SDE geodatabase.
Field Value Format Oracle Spatial Object Dataset Embed Connection Parameters Parameters specify Service Name or Easy Connect, Username, Password, Table List
When adding the reader, specify hard-coded values for the parameters listed above; they will be overridden by FME parameters at a later stage in the configuration.
Next: Define the Source Reader User Parameters
Define the Source Reader User Parameters
Configuring an FME Reader or FeatureReader transformer involves creating one or more published or private parameters and then, in some cases, linking those parameters to FME Reader properties. The parameters that are required differ according to which reader format type is being configured. The parameters and their properties are defined in the sections below, organized by reader format type.
- General comments
- Comma Separated Values (CSV)
- Esri File Geodatabase (GDB)
- Esri Geodatabase (SDE_GEODATABASE)
- Esri Shapefile (SHP)
- GeoJSON
- Microsoft Access Database (MDB_ADO)
- Microsoft Excel (XLSX)
- Oracle Spatial Object (SDO) and Non-Spatial
General comments
- Unless otherwise specified below, all User Parameters should have a default value assigned and the Optional checkbox left unchecked.
- Parameter types that are Scripted (Python) will automatically be defined as Private parameters.
- When readers are created some Published Parameters are automatically created; these must be renamed, as described below, in order to be used with the FME Framework.
- When specifying a source data file or directory in the standing area, specify the read-only path:
\\data.bcgov\data_staging_ro\bcgw
- When specifying multiple inputs, distinguish between them by changing the _1 suffix to _2, _3, etc. for inputs subsequent to the first.
- When adding a reader or specifying connection parameters in a transformer (e.g., SQLCreator, FeatureReader, DatabaseJoiner, etc.) choose the Embed Connection Parameters option in the Dataset: field. Then click on the Parameters button and specify hard-coded values for the connection parameters; they will be overridden by FME parameters at a later stage in the configuration.
Comma Separated Values (CSV)
This section describes the parameters that need to be defined when configuring a CSV reader.
Source CSV Path and Name (SRC_DATASET_CSV_1)
-
This parameter is for the source path and file name. When you add the CSV Reader to your FMW, FME will automatically create a published parameter with a name starting with “SourceDataset_CSV”. This parameter must be renamed:
- Find the published parameter in the FMW named SourceDataset_CSV
- Right click on it and choose “Edit Definition”
- Change the “Name” property of the parameter to SRC_DATASET_CSV_1
-
If you source more than one CSV increment the numbers at the end of the parameter name. Example:
- SRC_DATASET_CSV_1
- SRC_DATASET_CSV_2
- SRC_DATASET_CSV_3
-
The following table defines the properties for SRC_DATASET_CSV_1:
Field Value Published/Private published Type Filename (Existing) or Filename (Multiple) Name SRC_DATASET_CSV_1 Prompt Source CSV (Comma Separated Value) File(s) Configuration *.csv Attribute Assignment Default Default Value Path to the CSV that is being replicated Link From Reader → Source CSV (CommaSeparatedValue) File → Link to User Parameter → SRC_DATASET_CSV_1 -
When specifying a staging area file, use the read-only path (\data.bcgov\data_staging_ro\bcgw...
Next: Add the Destination Writer
Esri File Geodatabase (GDB)
This section describes the parameters that need to be defined when configuring a Esri File Geodatabase reader.
NOTE: If you are building an FMW that just reads from a FGDB and writes to the BCGW and does nothing in between but possibly rename columns and/or populate a primary key with a counter, then this ETL task might be doable using KIRK (Keeping Information Replicated Kontinuously). See Appendix 3.
Source FGDB Path and Name (SRC_DATASET_FGDB_1)
- This parameter describes the FGDB that contains the tables / feature classes that are to be read.
- Add the FGDB reader to the FMW that is being created.
- When the reader is created FME will automatically create a published parameter with a name starting with “SourceDataset_FILEGDB”.
Find this parameter and edit it so that its name is “SRC_DATASET_FGDB_1”
-
The following table defines the properties for SRC_DATASET_FGDB_1:
Field Value Published/Private published Type Folder (Existing) Name SRC_DATASET_FGDB_1 Prompt Source File Geodatabase Configuration N/A Attribute Assignment Default Default Value Path to the directory of the FGDB that is being read. Link From Reader → Source Geodatabase → Link to User Parameter → SRC_DATASET_FGDB_1
When specifying a staging area directory, use the read-only path (\data.bcgov\data_staging_ro\bcgw...
Source Feature Name (SRC_FEATURE_1)
- This parameter refers to the feature class that is being read in the FGDB described in the parameter SRC_DATASET_FGDB_1.
- After you have added the FileGeodb reader to the FMW:
- Create a new published parameter with the name SRC_FEATURE_1, and set the Default Value to the feature class that is being read by the reader.
- Now you need to link this parameter to the reader:
- Find the reader in the Navigator window (upper left) right click on the feature type and select properties.
- In the subsequent dialog, replace the value in General Parameters->Feature Class or Table Name with the string: $(SRC_FEATURE_1)
Multiple FGDB Feature Types
For FGDB sources it is important that you maintain a one to one relationship between SRC_DATASET_FGDB_# parameters and SRC_FEATURE_# parameters.
Example 1:
In this example there are two different file geodatabases (Census2012.gdb, and Census2011.gdb) and we need to include the feature type “census_data2016” and “census_data2013” from each of them.
- Create two readers, one for each FGDB
- Rename the parameters that refer to the FileGeodb to:
- SRC_DATASET_FGDB_1 (refers to Census2011.gdb)
- SRC_DATASET_FGDB_2 (refers to Census2013.gdb)
- Create the parameters below with the following default values:
- SRC_FEATURE_1 (Default Value=census_data2012)
- SRC_FEATURE_2 (Default Value=census_data2013)
- Finally ensure the SRC_FEATURE_1 are used by the reader by replacing references to the actual feature type with the string “$(SRC_FEATURE_1)” as described above.
Example 2:
In this example there is a single FGDB that contains multiple feature types that we want to read, for example “CensusMultiYear.gdb”, containing three feature classes: CensusData_2006, CensusData_2011, CensusData_2016.
-
Create the reader for the FGDB that reads multiple feature types.
-
Rename the parameter “SourceDataset_FILEGDB” to SRC_DATASET_FGDB_1 (referring to CensusMultiYear.gdb).
-
Now create two more parameters with the names:
- SRC_DATASET_FGDB_2
- SRC_DATASET_FGDB_3.
Set the Default Value for each of these to $(SRC_DATASET_FGDB_1). The Screen shot below shows you how to do this.
- Add three SRC_FEATURE_n parameters:
- SRC_FEATURE_1 (Default Value=CensusData_2006)
- SRC_FEATURE_2 (Default Value=CensusData_2011)
- SRC_FEATURE_3 (Default Value=CensusData_2016)
-
The following table defines the properties for SRC_FEATURE_n:
Field Value Published/Private published Type Text Name SRC_FEATURE_n Prompt The source feature class Configuration N/A Attribute Assignment Default Default Value The name of the feature class to read Link From Reader → Feature Types → Properties → Feature Class or Table Name
Next: Add the Destination Writer
Esri Geodatabase (SDE_GEODATABASE)
This section describes the parameters that need to be defined when configuring a Esri SDE Geodatabase reader.
Source Proxy Account Name (SRC_ORA_PROXY_SCHEMA)
-
Use this parameter to specify the proxy account used to read the source table/view. It will likely be different from the schema owner of the source table/view (SRC_ORA_SCHEMA).
Field Value Published/Private published Type Text Name SRC_ORA_PROXY_SCHEMA Prompt Proxy used to login to the source database Configuration N/A Attribute Assignment Default Default Value the proxy account used to log in, e.g., BCGW$XXXX Link From Reader → Parameters → Advanced → Override Username → Link to User Parameter → SRC_ORA_PROXY_SCHEMA
Source Schema Owner Name (SRC_ORA_SCHEMA)
-
Use this parameter to specify the schema that owns the source table/view.
Field Value Published/Private published Type Text Name SRC_ORA_SCHEMA Prompt Source Database Schema Configuration N/A Attribute Assignment Default Default Value The owner (schema) of the table or view to be read, e.g., FORESTVIEW Link From Reader → Feature Types → Properties → Feature Class or Table Name
Source Feature Name (SRC_FEATURE_1)
-
Use this parameter to define the feature type to be read. If multiple feature types are being read, create multiple parameters (SRC_FEATURE_1, SRC_FEATURE_2, etc.). See the examples in Multiple-FGDB-Feature-Types for a discussion on how to define multiple feature types.
Field Value Published/Private published Type Text Name SRC_FEATURE_1 Prompt Source table/view to be read and used in the replication ConfigurationN/A Attribute Assignment Default Default Value name of table or view, e.g., CUT_BLOCK_SHAPE Link From Reader → Feature Types → Properties → Feature Class or Table Name
Source Host (SRC_HOST)
-
Use this parameter to define the name of the host on which the source Oracle database resides.
Field Value Published/Private published Type Text Name SRC_HOST Prompt The source oracle database host Configuration N/A Attribute Assignment Default Default Value The source oracle database host, e.g., lrmbctsp.nrs.bcgov Link From nowhere - used internally by SRC_SDE_CONNECTIONFILE
Source Port (SRC_PORT)
-
This parameter is only needed if the source port is different from the standard Oracle port 1521.
Field Value Published/Private published Type Text Name SRC_PORT Prompt The port that the database listener is connected to Configuration N/A Attribute Assignment Default Default Value Oracle port number, when not 1521 Link From nowhere - used internally by SRC_SDE_CONNECTIONFILE
Source Service Name (SRC_ORA_SERVICENAME)
-
Field Value Published/Private published Type Text Name SRC_ORA_SERVICENAME Prompt Source Oracle service name Configuration N/A Attribute Assignment Default Default Value Source service name, e.g., DBP06.NRS.BCGOV Link From nowhere - used internally by SRC_SDE_CONNECTIONFILE
Source Schema Owner Password (SRC_ORA_PASSWORD)
-
Field Value Published/Private private Type Scripted (Python) Name SRC_ORA_PASSWORD Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
srcPass = params.getSourcePassword()
return srcPassLink From Reader → Parameters → Advanced → Override Username → Link to User Parameter → SRC_ORA_PASSWORD
Source Database Connection File (SRC_SDE_CONNECTIONFILE)
-
Field Value Published/Private private Type Scripted (Python) Name SRC_SDE_CONNECTIONFILE Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
connFile = params.getSrcDatabaseConnectionFilePath()
return connFileLink From Reader → Parameters → Connection File → Link to User Parameter → SRC_SDE_CONNECTIONFILE
When run from either DataBC’s FME Workbench workstation or DataBC’s FME Server machine, the connection file used by the SDE_GEODATABASE reader will get created automatically. The process will read the parameters SRC_HOST and SRC_ORA_SERVICENAME to calculate the name of the connection file. Then the framework will create those connection files. The connection file will be named
However, when run on the GTS environment, the framework will calculate the name of the sde connection file, but it will not attempt to create it. It will look for this connection file in the directory of the FMW file that is being run. For this to work the developer of the FMW will have to create the connection file either using arcpy or ArcCatalog. The name of the connection file should be
Next: Add the Destination Writer
Esri Shapefile (SHP)
- This section describes the parameters that need to be defined when configuring a Esri Shapefile reader.
- Rename the automatically created Published Parameter SourceDataset_SHP to SRC_DATASET_SHP_1.
Source Shapefile Name (SRC_DATASET_SHP_1)
-
Field Value Published/Private published Type Filename (Existing) Name SRC_DATASET_SHP_1 Prompt Source Esri Shapefile(s) Configuration *.shp Attribute Assignment Default Default Value Path of the Shapefile (with .shp extension), e.g.,\data.bcgov\data_staging_ro\BCGW\administrative_boundaries\ALC_Panel_regions.shp Link From Reader → Source Esri Shape File(s) → Link to User Parameter → SRC_DATASET_SHP_1
Source Feature Name (SRC_FEATURE_1)
-
Field Value Published/Private published Type Text Name SRC_FEATURE_1 Prompt Source Feature class Configuration N/A Attribute Assignment Default Default Value Name of the shapefile without the .shp extension, e.g., ALC_Panel_regions Link From N/A
Next: Add the Destination Writer
GeoJSON
- This section describes the parameters that need to be defined when configuring a GeoJSON reader.
- Rename the automatically created Published Parameter SourceDataset_GEOJSON to SRC_GEOJSON_FILE_1.
Source GeoJSON File Name (SRC_GEOJSON_FILE_1)
-
Field Value Published/Private published Type N/A Name SRC_GEOJSON_FILE_1 Prompt Source GeoJSON File or URL Configuration N/A Attribute Assignment N/A Default Value Source GeoJSON File or URL, e.g., https://gwells-prod.pathfinder.gov.bc.ca/gwells/api/v1/gis/aquifers Link From Reader → Source GeoJSON File or URL → Link to User Parameter → SRC_GEOJSON_FILE_1
Microsoft Access Database (MDB_ADO)
This section describes the parameters that need to be defined when configuring a Microsoft Access database reader.
Source Access Database File Name (SRC_DATASET_MDBADO_1)
- This parameter is for the source path and file name.
-
Rename the automatically created Published Parameter SourceDataset_ADO_MDB to SRC_DATASET_MDBADO_1.
Field Value Published/Private published Type Filename (Multiple) Name SRC_DATASET_MDBADO_1 Prompt Source Access Database Configuration *.mdb, *.accdb Attribute Assignment Default Value Path to the .mdb or .accdb file that is being read. Link From Reader → Source Microsoft Access Database File(s) → Link to User Parameter → SRC_DATASET_MDBADO_1
Source Feature Name (SRC_FEATURE_1)
-
This parameter is for the source table or view.
Field Value Published/Private published Type Text Name SRC_FEATURE_1 Prompt The source table Configuration N/A Attribute Assignment Default Default Value the feature class that is being read by the reader, e.g., BH_SITE_LITHOLOGY_VIEW Link From
Next: Add the Destination Writer
Microsoft Excel (XLSX)
This section describes the parameters that need to be defined when configuring a Microsoft Excel reader.
Source Excel File Name (SRC_DATASET_XLS_1)
- This parameter is for the source path and file name.
-
Rename the automatically created Published Parameter SourceDataset_XLSR to SRC_DATASET_XLS_1.
Field Value Published/Private published Type Filename (Multiple) Name SRC_DATASET_XLS_1 Prompt Source Microsoft Excel File(s) Configuration *.xlsx Attribute Assignment Default Default Value Path to source Excel file Link From Reader → Source Microsoft Excel File(s) → Link to User Parameter → SRC_DATASET_XLS_1
Source Feature Name (SRC_FEATURE_1)
-
This parameter is for the source sheet within the Excel file
Field Value Published/Private published Type Text Name SRC_FEATURE_1 Prompt The source Excel file sheet name Configuration N/A Attribute Assignment Default Default Value the name of the sheet that is being read by the reader, e.g., BC_HEX_V3, Sheet1 Link From Reader → Feature Types → Properties -> Sheet Name
Next: Add the Destination Writer
Oracle Spatial Object (SDO) and Non-Spatial
This section applies to any reader that natively connects to an Oracle database. This includes:
- Oracle Non-Spatial (ORACLE_NONSPATIAL)
- Oracle Spatial Object (ORACLE_SPATIAL)
Source Proxy Account Name (SRC_ORA_PROXY_SCHEMA)
-
Use this parameter to specify the proxy account used to read the source table/view. It will likely be different from the schema owner of the source table/view.
Field Value Published/Private published Type Text Name SRC_ORA_PROXY_SCHEMA Prompt Proxy used to login to the source database Configuration N/A Attribute Assignment Default Default Value the proxy account used to log in, e.g., BCGW$XXXX Link From Reader → Parameters → Advanced → Override Username → Link to User Parameter → SRC_ORA_PROXY_SCHEMA
Source Schema Owner Name (SRC_ORA_SCHEMA)
-
Use this parameter to specify the schema that owns the source table/view.
Field Value Published/Private published Type Text Name SRC_ORA_SCHEMA Prompt Source Database Schema Configuration N/A Attribute Assignment Default Default Value The owner (schema) of the table or view to be read, e.g., FORESTVIEW Link From Reader → Feature Types → Properties → Feature Class or Table Name
Source Feature Name (SRC_FEATURE_1)
-
Use this parameter to specify the Oracle table or view being read.
Field Value Published/Private published Type Text Name SRC_FEATURE_1 Prompt Source table/view to be read and used in the replication Configuration N/A Attribute Assignment Default Default Value name of table or view, e.g., CUT_BLOCK_SHAPE Link From Reader → Feature Types → Properties → Feature Class or Table Name
Source Service Name (SRC_ORA_SERVICENAME)
-
Source Oracle service name
Field Value Published/Private published Type Text Name SRC_ORA_SERVICENAME Prompt Source Oracle service name Configuration N/A Attribute Assignment Default Default Value Source service name, e.g., DBP06.NRS.BCGOV Link From nowhere - used internally by SRC_EASYCONNECT
Source Host (SRC_HOST)
-
Source host
Field Value Published/Private published Type Text Name SRC_HOST Prompt The source oracle database host Configuration N/A Attribute Assignment Default Default Value The source oracle database host, e.g., lrmbctsp.nrs.bcgov Link From nowhere - used internally by SRC_EASYCONNECT
Source Port (SRC_PORT)
-
This parameter is only needed if the source port is different from the standard Oracle port 1521.
Field Value Published/Private published Type Text Name SRC_PORT Prompt The port that the database listener is connected to Configuration N/A Attribute Assignment Default Default Value Oracle port number, when not 1521 Link From nowhere - used internally by SRC_EASYCONNECT
Source Schema Owner Password (SRC_ORA_PASSWORD)
-
Source Oracle password
Field Value Published/Private private Type Scripted (Python) Name SRC_ORA_PASSWORD Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
srcPass = params.getSourcePassword()
return srcPassLink From Reader → Parameters → Advanced → Override Username → Link to User Parameter → SRC_ORA_PASSWORD
Source Easy Connect String (SRC_EASYCONNECT)
-
Source Easy Connect string
Field Value Published/Private private Type Scripted (Python) Name SRC_EASYCONNECT Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
ecStr = params.getSrcEasyConnectString()
return ecStrLink From Reader → Parameters → Service Name or Easy Connect → Link to User Parameter → SRC_EASYCONNECT
Add the Destination Writer
In general, FME scripts use FME Writers to write to destination datasets. There are, however, occasions where it is preferable to use a FeatureWriter transformer instead of an FME Writer. In particular, if the script implements a series of consecutive read-transform-write patterns, with subsequent read-transform-writes being executed depending on the success of earlier ones, then it is preferable to use FeatureReader and FeatureWriter transformers in a single script rather than using traditional Readers and Writers in multiple scripts called by FMEServerJobSubmitter.
Esri SDE Geodatabase (GEODATABASE_SDE)
-
Adding this writer will take you through a series of panels, each of which include some of the fields defined below.
Field/Panel Value/Action Format Esri Geodatabase (ArcSDE Geodb) Dataset N/A Feature Class or Table Definition select Import from Dataset… Esri Geodatabase (ArcSDE Geodb) Parameters panel Enter Connection File name, check Override Credentials box, and enter Username and Password, select the table to be written to Feature Type Panel Change Truncate Table to Yes
The connection file specified can be one generated using ArcCatalog or ArcPy, pointing to the BCGW delivery environment. This hard-coded connection file, and the specified username and password will be overridden in a subsequent step.
The Feature Type Panel noted above can be clicking the sprocket on the writer icon in the Main window after adding the writer.
Next: Define the Destination Writer User Parameters
Oracle Non-Spatial
-
Adding this writer will take you through a series of panels, each of which include some of the fields defined below.
Field/Panel Value/Action Format Oracle Non-Spatial Dataset select Embed Connection Parameters Parameters enter Service Name or Easy Connect (e.g., bcgw-i.bcgov/dwdlvr1.bcgov), Username and Password Table Definition select Import from Dataset… Oracle Spatial Object Parameters Panel select the name of the table to be written Feature Type Panel Change Table Handling to Truncate Existing
The hard-coded username and password entered will be overridden in a subsequent step.
The Feature Type Panel noted above can be reached by clicking the sprocket on the writer icon after adding the writer,
Next: Define the Destination Writer User Parameters
Oracle Spatial Object (SDO)
-
Adding this writer will take you through a series of panels, each of which include some of the fields defined below.
-
This writer is to be used only in exceptional circumstances. Contact DataBC before using.
Field/Panel Value/Action Format Oracle Spatial Object Dataset select Embed Connection Parameters Parameters enter Service Name or Easy Connect (e.g., bcgw-i.bcgov/dwdlvr1.bcgov), Username and Password Table Definition select Import from Dataset… Oracle Spatial Object Parameters Panel select the name of the table to be written Feature Type Panel Change Table Handling to Truncate Existing
The hard-coded username and password entered will be overridden in a subsequent step.
The Feature Type Panel noted above can be clicking the sprocket on the writer icon after adding the writer,
Next: Define the Destination Writer User Parameters
Define the Destination Writer User Parameters
Once defined, some parameters will be linked to items in the Navigation window.
When linking to User Parameters:
- Right click on the item you want to link from in the Navigator window.
- Select Link to User Parameter
- Select the associated user parameter name in drop down list
- Click OK
- Parameters Applying to All Writers
- Oracle (SPATIAL AND NON-SPATIAL)
- Esri SDE Geodatabase (SDE_GEODATABASE)
Parameters Applying to All Writers
- The following parameters are required for all writers writing to the BCGW.
Destination Database Keyword (DEST_DB_ENV_KEY)
-
This parameter is used internally when calculating the destination host, service name, and password corresponding to the environment being loaded into (delivery, test, or production). A value of OTHER can be used when preparing a script that writes to a non-BCGW destination (e.g., a file). When run on a DataBC workstation or FME Server the password is fetched from the DataBC password management system. When run in the GTS environment, the password will be fetched from the dbCreds.json that you supply as described in If developing on a GTS server, configure a dbCreds.json file.
Field Value Published/Private published Type Choice or Text Name DEST_DB_ENV_KEY Prompt Destination Database Keyword (DLV,TST,PRD,OTHER) Configuration DLV%TST%PRD%OTHER Attribute Assignment Default Default Value DLV Link From N/A
Destination Schema Owner (DEST_SCHEMA)
-
This parameter identifies the schema containing the BCGW table being loaded into.
Field Value Published/Private published Type Text Name DEST_SCHEMA Prompt Destination Schema Configuration N/A Attribute Assignment Default Default Value Schema name (owner) of the BCGW table to be written Link From Writer → Parameters → Advanced → Override Username → Link to User Parameter → DEST_SCHEMA Also, for the corresponding feature type (writer → Feature Types), update the Feature Class or Table Name in the properties to $(DEST_SCHEMA).$(DEST_FEATURE_1) (or $(DEST_SCHEMA).$(DEST_FEATURE_2), etc.)
Destination Feature Object Name 1 (DEST_FEATURE_1)
-
If there is more than one destination object, create a parameter for each and increment the number, e.g., DEST_FEATURE_2.
Field Value Published/Private published Type Text Name DEST_FEATURE_1 Prompt Destination Feature 1 Configuration N/A Attribute Assignment Default Default Value Name of the BCGW table to be written For the corresponding feature type (writer → Feature Types), update the Feature Class or Table Name in the properties to $(DEST_SCHEMA).$(DEST_FEATURE_1) (or $(DEST_SCHEMA).$(DEST_FEATURE_2), etc.)
Destination Schema Password (DEST_PASSWORD)
-
This parameter is used to calculate the password for DEST_SCHEMA, either from the DataBC password management system (when run on a DataBC workstation or FME Server) or the dbCreds.json file (when run on a GTS server).
Field Value Published/Private private Type Scripted (Python) Name DEST_PASSWORD Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getDestinationPassword()Link From Writer → Parameters → Advanced → Override Password → Link to User Parameter → DEST_PASSWORD
Next: Configure the FMW Framework Parameters
Oracle (SPATIAL AND NON-SPATIAL)
The following parameters are required when using the FME native Oracle Spatial and Non-spatial writers.
NOTE: The Oracle native spatial writer should only be used in exceptional circumstances. Consult DataBC before using. The Non-spatial writer should be used for unregistered, non-spatial BCGW tables.
Destination Easy Connect String (DEST_EASYCONNECT)
-
This parameter builds the EZ Connect string used by native Oracle FME writers.
Field Value Published/Private private Type Scripted (Python) Name Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getDestEasyConnectString()Link From Writer → Service Name or Easy Connect → Link to User Parameter → DEST_EASYCONNECT
Destination Host (DEST_HOST)
-
This parameter is used in building DEST_EASYCONNECT.
Field Value Published/Private private Type Scripted (Python) Name Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getDestinationHost()Link From N/A
Destination Service Name (DEST_SERVICENAME)
-
This parameter is used in building DEST_EASYCONNECT.
Field Value Published/Private private Type Scripted (Python) Name Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getDestinationServiceName()Link From N/A
Next: Configure the FMW Framework Parameters
Esri SDE Geodatabase (SDE_GEODATABASE)
The following parameters are required for the Esri SDE Geodatabase writer.
Destination Database Connection File (DEST_SDE_CONNFILE)
-
This parameter is used to calculate the name and contents of an Esri connection file to use when connecting to the BCGW.
-
When run from either DataBC’s FME Workbench workstation or DataBC’s FME Server machine, the connection file used by the SDE_GEODATABASE reader will get created automatically. The process will read the parameters DEST_HOST and DEST_ORA_SERVICENAME to calculate the name of the connection file. Then the framework will create those connection files. The connection file will be named
__ .sde -
However, when run on the GTS environment, the framework will calculate the name of the sde connection file, but it will not attempt to create it. It will look for this connection file in the directory of the FMW file that is being run. For this to work the developer of the FMW will have to create the connection file either using arcpy or ArcCatalog. The name of the connection file should be
__ .sde, e.g., bcgw-i.bcgov__idwdlvr1.sde. If the name of your connection file is incorrect you will get an error message that tells you what name the framework is expecting the connection file to be called. Field Value Published/Private private Type Scripted (Python) Name DEST_SDE_CONNFILE Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getDestDatabaseConnectionFilePath()Link From Writer → Connection File → Link to User Parameter → DEST_SDE_CONNFILE
Failed Features (FAILED_FEATURES)
-
This parameter is used to calculate the file path where the failed features files (FFS) will get stored. This parameter should be linked to the Geodatabase Writer parameter called “Failed Features Dump filename” (Geodatabase Writer->Parameters->Advanced->Failed Features Dump filename). In addition, the Writer parameter “Ignore Failed Features” should be set to “Yes” (which allows the script to continue after failed features are encountered), and also the Writer parameter “Dump Failed Features to File” needs to be set to “Yes” (which, along with the Failed Feature Dump filename”, would allow the failed features to be saved out to an .FFS file for troubleshooting purposes).
-
When run on a DataBC machine (FME Workbench or FME Server), the path will be $TEMPLATEHOME/outputs\failed<FMW name><DEST_SCHEMA>_
_<job#>_failedFeatures.ffs On workbench there is no job number so that value will be null. -
When run on a non-DataBC machine the path will be calculated relative to the path where the FMW resides.
i.e., .\outputs\failed<FMW name><DEST_SCHEMA>_
-
If the FMW has more than one DEST_FEATURE, e.g., DEST_FEATURE_1 and DEST_FEATURE_2, then to create an FFS file for the second destination change the line
return params.getFailedFeaturesFile()
toreturn params.getFailedFeaturesFile(2)
.Field Value Published/Private private Type Scripted (Python) Name FAILED_FEATURES Prompt N/A Configuration N/A Attribute Assignment Default Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getFailedFeaturesFile()Link From Writer → Parameters → Advanced → Failed Feature Dump filename → Link to User Parameter → FAILED_FEATURES
Next: Configure the FMW Framework Parameters
Configure the FMW Framework Parameters
When linking to User Parameters:
- Right click on the item you want to link from in the Navigator window.
- Select Link to User Parameter
- Select the associated user parameter name in drop down list
- Click OK
Log File (LOGFILE)
-
This parameter will populate the location for the log file when the script is run from FME Workbench. This parameter is ignored when FMW’s are run from FME Server. The location for the log file will be an “./outputs/log” folder in the same folder as the FMW that is being run. The code will create the folder if it does not already exist.
Field Value Published/Private private Type Scripted (Python) Name LOGFILE Prompt N/A Configuration N/A Attribute Assignment Default Value import DataBCFMWTemplate
params = DataBCFMWTemplate.CalcParams(FME_MacroValues)
return params.getFMWLogFileRelativePath()Link From Workspace Parameters → Logging → Log File. See Logging
File Change Detection (FILE_CHANGE_DETECTION)
-
The File Change Detector transformer can be used to bypass loading from a file if the file has not changed since it was last loaded. Set the FILE_CHANGE_DETECTION to FALSE to bypass the transformer (and force a reload even if the file hasn’t changed).
Field Value Published/Private published Type Choice Name FILE_CHANGE_DETECTION Prompt Set to TRUE to enable (else disable) change detection Configuration TRUE%FALSE Attribute Assignment Default Default Value TRUE Link From N/A
Next: Define the Workspace Parameters
Define the Workspace Parameters
-
Define the following workstation parameters in the Workstation Parameters section of the Navigator tree.
- Name
- Logging
- Scripting → Startup Python Script
- Scripting → Shutdown Python Script
Name
Enter the name of the FMW without the .fmw file extension. See FMW File Naming.
Logging
In the Navigator:
- Expand Workspace Parameters and then Logging
- Right click **Log File:
** - Link to User Parameter… LOGFILE
Scripting → Startup Python Script
In the Navigator:
- Expand Workspace Parameters and then Scripting
- Double click **Startup Python Script:
** - Copy and paste the text below:
- Click OK
import fme
import DataBCFMWTemplate
start = DataBCFMWTemplate.Start(fme)
start.startup()
Scripting → Shutdown Python Script
In the Navigator:
- Expand Workspace Parameters and then Scripting
- Double click **Shutdown Python Script:
** - Copy and paste the text below
- Click OK
import fme
import DataBCFMWTemplate
shutIt = DataBCFMWTemplate.Shutdown(fme)
shutIt.shutdown()
Next: Add the Transformers
Add the Transformers
This section describes commonly used FME transformers and provides guidelines for their use.
- File Change Detector v2
- BCDC File Change Detector
- Logger
- Tester
- Reader and Writer Transformers, SQL Transformers, DatabaseJoiner
- Vertex Creator
- Attribute Manager
- Counter
File Change Detector v2
- This transformer is normally the first transformer after a Reader. It filters records into two output ports: CHANGES and NO_CHANGES. All records from the file are output to the CHANGES port if the file has changed since the file was last successfully loaded into the same destination environment. All records from the file are output to the NO_CHANGES port if the file has not changed since the file was last successfully loaded into the same destination environment. Note that this transformer filters based on the status of the file, and not on whether individual records have changed.
BCDC File Change Detector
- This transformer is like File Change Detector v2 except that it is used when reading from a resource in the BC Data Catalogue.
Logger
- Logger transformers are often connected to Tester transformers that test for exceptional situations. Because the Logger transform creates an FFS file, care should be taken to restrict their use to logging abnormal (error) situations.
Tester
- The Tester transformer is usually used to route features through different flows and to check for exceptional (error situations). Tester transforms should be annotated with a comment so that it is clear what is being tested.
Provincial Bounding Box
- For point datasets where the points lie in British Columbia, this should be tested as shown in the following diagram:
![]{images/image_testers.png)
Reader and Writer Transformers, SQL Transformers, DatabaseJoiner
- Several types of transformers connect to source and destination datasets/databases. When configuring these, hard-code the connection parameters so that the lists of source and destination columns are populated. As a final step, though, find these transformers in the Transformers section in the Navigator pane, and update the hard-coded values with the appropriate published or private parameters, as illustrated below:
Vertex Creator
- Spreadsheets of point data (with a latitude and longitude column) can be spatialized by building a point from the latitude and longitude, as shown in the following diagram. Note the relationship between X/Y and Longitude/Latitude.
Attribute Manager
- Often the source dataset has more columns than the target table, and the columns that match may have different names. Use an Attribute Manager transform, defined so that its output matches the columns and their names in the target table.
Counter
- Counter transformers are often used to populate a primary key.
APPENDIX 1 - NOTIFICATIONS
The framework as of version 1.0.15, can now do email notifications. Currently there are three different notification types. Each of these is defined in the following subsections. Notifications work through the definitions of one of the three published parameters NOTIFY_ALL, NOTIFY_FAILURE, NOTIFY_SUCCESS. The shutdown method will determine the status of the job, and then look for any of the following parameters to be defined. If they are defined the shutdown procedure will parse out the email lists from the parameters and send notifications out based on the status (success/failure) of the job.
Notifications should be defined as:
Type*: *Text (Multiline)
Name*:
Prompt*: *emails for job notifications
Default Value*: *<enter your email list, one email per line>
The emails sent will include an attachment with the job log.
NOTIFY_ALL
Prompt: Notifications for all replication events
Emails defined in this parameter will be sent out regardless of what happens with the job. The only exceptions would be if there is a failure associated with the population of published parameters.
NOTIFY_FAILURE
Prompt: Notifications for failure
Emails defined in this parameter will be sent out ONLY if the job FAILS. If the job completes successfully and FME Server tells us that the jobs succeeded then this list of emails will NOT be sent out.
NOTIFY_SUCCESS
Prompt: Notifications for success
Emails defined in this parameter will be sent out ONLY if the job SUCCEEDS.
APPENDIX 2 - NON-BCGW DESTINATIONS
The proceeding sections dealt with the cases where FMWs are being written to load BCGW tables. Some DataBC FMWs write to the BC Data Catalogue and https://pub.data.gov.bc.ca/datasets and/or other file systems. This section describes the parameters to use in those situations.
BC Data Catalogue File Store
–under construction–
CSV, SHP, FGDB Files, and AGO
Comma Separated Value (CSV)
-
Field Value Format CSV (Comma Separated Value) Dataset Path of the directory where file will be created/stored Parameters Defaults should be fine Feature Type Specify CSV File Name
After adding the writer, define the following published parameter:
Destination CSV Path and Name (DEST_DATASET_CSV_1)
- This parameter is for the destination path and file name. When you add the CSV Reader to your FMW, FME will automatically create a published parameter with a name starting with “DestDataset_CSV”. This parameter must be renamed:
- Find the published parameter in the FMW name starting with DestDataset_CSV
- Right click on it and choose “Edit Definition”
- Change the “Name” property of the parameter to DEST_DATASET_CSV_1
- If you source more than one CSV increment the numbers at the end of the parameter name. Example:
- DEST_DATASET_CSV_1
- DEST_DATASET_CSV_2
- DEST_DATASET_CSV_3
-
The following table defines the properties for DEST_DATASET_CSV_1:
Field Value Published/Private published Type Filename (Existing) or Filename (Multiple) Name DEST_DATASET_CSV_1 Prompt Destination CSV (Comma Separated Value) File(s) Configuration *.csv Attribute Assignment Default Default Value Path to the CSV that is being produced Link From Writer → Destination CSV (CommaSeparatedValue) File → Link to User Parameter → DEST_DATASET_CSV_1
Shapefile (SHAPE)
-
Field Value Format Esri Shapefile Dataset Path of the zip file where the shapefile components will be created/stored Parameters Defaults should be fine Feature Type Specify shapefile name, without the .shp extension
After adding the writer, define the following published parameters:
Destination Shapefile Name (DEST_DATASET_SHP_1)
-
Field Value Published/Private published Type Filename (Existing) Name DEST_DATASET_SHP_1 Prompt Destination Esri Shapefile(s) Configuration *.shp Attribute Assignment Default Default Value Path of the shapefile (with .shp extension) Link From Writer → Destination Esri Shape File(s) → Link to User Parameter → DEST_DATASET_SHP_1
Destination Feature Name (DEST_FEATURE_1)
-
Field Value Published/Private published Type Text Name DEST_FEATURE_1 Prompt Destination Feature class Configuration N/A Attribute Assignment Default Default Value Name of the shapefile without the .shp extension, e.g., ALC_Panel_regions Link From N/A
Esri FILE Geodatabase (GEODATABASE_FILE)
-
Field Value Format Esri Geodatabase (File Geodb) Dataset specify Geodatabase (.gdb) folder Parameters check Overwrite Existing Geodatabase box Feature Type specify Feature Class or Table Name
select Geometry
Drop Table = No
Truncate Table = Yes
Specify name of Feature Dataset
After adding the writer, define the following published parameters:
Destination FGDB Path and Name (DEST_DATASET_FGDB_1)
- This parameter describes the fgdb that contains the tables / feature classes that are to be read.
- Add the FGDB reader to the FMW that is being created.
- When the reader is created FME will automatically create a published parameter with a name starting with “DestinationDataset_FILEGDB”.
Find this parameter and edit it so that its name is “DEST_DATASET_FGDB_1”
-
The following table defines the properties for DEST_DATASET_FGDB_1:
Field Value Published/Private published Type Folder (Existing) Name DEST_DATASET_FGDB_1 Prompt Destination File Geodatabase Configuration N/A Attribute Assignment Default Default Value Path to the directory of the FGDB that is being written. Link From Writer → Destination Geodatabase → Link to User Parameter → DEST_DATASET_FGDB_1
When specifying a staging area directory, use the read-only path (\data.bcgov\data_staging_ro\bcgw...
Destination Feature Name (DEST_FEATURE_1)
- This parameter refers to the feature class that is being written in the FGDB described in the parameter DEST_DATASET_FGDB_1.
- After you have added the FileGeodb reader to the FMW:
- Create a new published parameter with the name DEST_FEATURE_1, and set the Default Value to the feature class that is being written by the writer.
- Now you need to link this parameter to the writer:
- Find the writer in the Navigator window (upper left) right click on the feature type and select properties.
- In the subsequent dialog, replace the value in General Parameters->Feature Class or Table Name with the string: $(DEST_FEATURE_1)
BC Map Hub (AGO)
-
Authoring in FME Desktop 2017:
Field Value Writer Esri ArcGIS Portal Feature Service ARCGISPORTALFEATURES Dataset Path of the AGO Portal Host (https://governmentofbc.maps.arcgis.com) Parameters Authentication Type: Generate Token - User: AGO Account Username - Password: AGO Account Password Feature Service AGO Feature Service name -
Authoring in FME Desktop 2020:
Field Value Format Esri ArcGIS Online Hosted Feature Layer ARCGISONLINEFEATURES Dataset Path of the AGO hosted item file where the AGO components will be stored Parameters Defaults should be fine Feature Type Specify AGO Hosted Feature Layer name, without the .?? extension
After adding the writer, define the following published parameters:
Destination ArcGIS Host (DEST_HOST)
-
Field Value Published/Private published Type Text Name DEST_HOST Prompt Destination ArcGIS Online Portal URL Configuration *. Attribute Assignment Default Default Value https://governmentofbc.maps.arcgis.com Link From Writer → Portal URL → Link to User Parameter → DEST_HOST
Destination ArcGIS Username (DEST_AGO_USERNAME)
-
Field Value Published/Private published Type Text Name DEST_AGO_USERNAME Prompt Destination ArcGIS Online Username Configuration *. Attribute Assignment Default Default Value [AGO Account Username] Link From Writer → Parameters → User → Link to User Parameter → DEST_AGO_USERNAME
Destination ArcGIS Name (DEST_DATASET_AGO_1)
-
Field Value Published/Private published Type Text Name DEST_DATASET_AGO_1 Prompt Destination AGO Feature Service Configuration *. Attribute Assignment Default Default Value [path for the Feature Service] Link From Writer → Parameters → Feature Service → Link to User Parameter → DEST_DATASET_AGO_1
Destination Feature Name (DEST_FEATURE_1)
-
Field Value Published/Private published Type Text Name DEST_FEATURE_1 Prompt Destination Feature 1 Configuration N/A Attribute Assignment Default Default Value Name of the AGO Hosted Feature Layer Link From Writer → Feature Types → Properties → Layer Name → $(DEST_FEATURE_1)
Define the following private parameters:
Destination ArcGIS Password (DEST_AGO_PASSWORD)
-
Field Value Published/Private private Type Password Name DEST_AGO_PASSWORD Prompt Destination ArcGIS Online Password Configuration *. Attribute Assignment Default Default Value [AGO Account Password] Link From Writer → Parameters → Password → Link to User Parameter → DEST_AGO_PASSWORD
APPENDIX 3 - KIRK PREPARATION
Many DataBC ETL jobs do nothing more than read a file geodatabase, write to the BCGW, and possibly rename columns and populate a primary key value. ETL jobs that fit this pattern can be implemented using KIRK (Keeping Information Replicated Kontinuously) rather than writing a separate FMW. KIRK does use an underlying FMW, but it is parameterized, allowing it to be used with different FGDB sources and BCGW destinations.
Currently, the process of defining a KIRK job is manual, and it must be done by DataBC staff. So if you are a vendor or employee external to DataBC you must still delivery a working FMW. But that FMW does not need to follow any standards - you just need to verify that it works in BCGW Delivery. Include the FMW in your delivery kit, along with a spreadsheet having the following information:
FIELD | Example value |
---|---|
Source FGDB | \data.bcgov\data_staging_ro\BCGW\administrative_boundaries\GBA_Administrative_Boundaries.gdb |
Source Feature Class | IMPROVEMENT_DISTRICTS_SP_SECURE |
Destination Schema | WHSE_LEGAL_ADMIN_BOUNDARIES |
Destination Table | ABMS_IMPROVEMENT_DISTRICTS_SP |
Transformation Name | abms_improvement_districts_sp_staging_gdb_bcgw |
Counter Column | ABMS_IDS_SYSID |
Counter Start # | 1 |
Cron Expression | 0 0 23 ? * SUN * |
If the source FGDB columns map to BCGW columns having different names, then a field map is required as well, as shown in this example:
FGDB Column Name | BCGW Column Name |
---|---|
ADMIN_AREA_ID | LGL_ADMIN_AREA_ID |
ADMIN_AREA_PARENT_NAME | ADMIN_AREA_GROUP_NAME |
UPDATE_DATE | WHEN_UPDATED |
IS_PUBLIC | PUBLIC_IND |
APPENDIX 4 - FINAL PUBLICATION CHECKLIST
Before submitting your FMW to DataBC, check the following:
General
- FMW has been tested in BCGW delivery.
- FMWs have been supplied in the
dataload
directory of the delivery kit. - dbCreds.json file and
_ .sde files have been removed from the delivery kit.
Reader/Writer Published and Private Parameters
The following parameters have been defined:
Framework Parameters
- DEST_DB_ENV_KEY has been set to DLV
- FILE_CHANGE_DETECTION has been set to TRUE
- Writer → Workspace Parameters → Logging → Log File has been linked to LOGFILE
If the fmw will not be scheduled, you have the option to add an additional parameter:
Field | Value |
---|---|
Published/Private | published |
Type | Text |
Name | FMW_COMMENTS |
Prompt | Enter comments: |
Configuration | N/A |
Attribute Assignment | Default |
Default Value | Add comments describing any relevant details required for the user to know when running the fmw. |
Link From | N/A |
Source Readers
- Source file is linked to the appropriate parameter.
- For staging area sources, the read-only path is used.
- Feature type name is linked to the appropriate parameter (except for CSV sources).
Destination Writers
Writer parameters have been set or linked to the appropriate Writer properties. The checklist items depend on which Writer is used.
SDE Geodatabase Writer
- Writer → Connection File has been linked to DEST_SDE_CONNFILE
- Writer → Advanced → Failed Feature Dump filename has been linked to FAILED_FEATURES
- Writer → Advanced → Override Username has been linked to DEST_SCHEMA
- Writer → Advanced → Override Password has been linked to DEST_PASSWORD
- Writer → Feature Types →
→ Feature Class or Table Name has been set to **$(DEST_SCHEMA).$(DEST_FEATURE_1)** - Writer → Feature Types →
→ Drop Table has been set to **No** - Writer → Feature Types →
→ Truncate Table has been set **Yes**
Oracle Spatial Writer
- Writer → Service Name or Easy Connect has been linked to DEST_EASYCONNECT
- Writer → Parameters → Username has been linked to DEST_SCHEMA
- Writer → Parameters → Password has been linked to DEST_PASSWORD
- Writer → Feature Types →
→ Table Name has been set to **$(DEST_SCHEMA).$(DEST_FEATURE_1)** - Writer → Feature Types →
→ Create Table has been set to **No** - Writer → Feature Types →
→ Truncate Existing Table has been set **Yes**
Oracle Non-spatial Writer
- Writer → Service Name or Easy Connect has been linked to DEST_EASYCONNECT
- Writer → Parameters → Username has been linked to DEST_SCHEMA
- Writer → Parameters → Password has been linked to DEST_PASSWORD
- Writer → Feature Types →
→ Table Name has been set to **$(DEST_SCHEMA).$(DEST_FEATURE_1)** - Writer → Feature Types →
→ Feature Operation has been set to **Insert** - Writer → Feature Types →
→ Table Handling has been set **Truncate Existing**
Workspace Parameters
- Workspace Parameters → Name: Name of the FMW without file extension
- Workspace Parameters → Logging → Log File: linked to LOGFILE parameter
- Workspace Parameters → Scripting → Startup Python Script: set to python code
- Workspace Parameters → Scripting → Shutdown Python Script: set to python code
Documentation
- Annotations are used to explain complex logic and Tester transformer tests.
- For complex scripts (> 10 transformers) Bookmarks are used to document flows.