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.

Table of Contents

• ACCESSING THE FME FRAMEWORK
  • Allow Reader Feature Type Editing
  • Enable FME Framework Customizations
  • Set the Path to the Python Scripts Underlying the FME Framework
• CREATING AN FMW
  • If developing on a GTS server, configure a dbCreds.json file
  • Add the Source Reader(s)
  • Define the Source Reader User Parameters
  • Add the Destination Writer
  • Define the Destination Writer User Parameters
  • Configure the FMW Framework Parameters
  • Define the Workspace Parameters
  • Add the Transformers
• APPENDIX 1 - NOTIFICATIONS
• APPENDIX 2 - NON-BCGW DESTINATIONS
  • BC Data Catalogue File Store
  • CSV, SHP, FGDB Files, and AGO
    • Comma Separated Value (CSV)
    • Shapefile (SHAPE)
    • Esri FILE Geodatabase (GEODATABASE_FILE)
    • BC Map Hub (AGO)
• APPENDIX 3 - KIRK PREPARATION
• APPENDIX 4 - FINAL PUBLICATION CHECKLIST
  • General
  • Reader/Writer Published and Private Parameters
  • Framework Parameters
  • Source Readers
  • Destination Writers
    • SDE Geodatabase Writer
    • Oracle Spatial Writer
    • Oracle Non-spatial Writer
  • Workspace Parameters
  • Documentation
  • Illustration of Configuring an FME Script

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:

1. Open FME Workbench and from the menu bar
2. Choose Tools → FME Options
3. Select Workbench in the left hand panel of the FME Options popup
4. 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

1. Open FME Workbench and on the menu bar
2. Choose Tools → FME Options
3. Select Default Paths in the left hand panel of the FME Options popup
4. Click on the + symbol in the Shared FME Folders section
5. Enter this directory: \data.bcgov\work\scripts\python\DataBCFmeTemplate2\fmeCustomizations
6. Click YES on the popup to create one or more of the required subfolders
7. Click OK

Set the Path to the Python Scripts Underlying the FME Framework

1. Open the Windows Command Prompt window (in the Start Menu list under the Windows System folder)
2. Check if you have already defined the PYTHONPATH user environment variable
   1. Type the set command at the prompt
   2. Look for a variable listed called PYTHONPATH and if it is set to the correct location
3. If there is no PYTHONPATH variable, then you’ll create it now
   1. At the prompt type the following:
      setx PYTHONPATH "\\data.bcgov\work\scripts\python\DataBCFmeTemplate2\"
   2. A SUCCESS message should be displayed
   3. Note: a new/updated environment variable won’t be visible in the set list until after you exit the Commond Prompt window
4. Exit the Command Prompt window

RETURN TO TOP

CREATING AN FMW

• Start with a new FMW or one of the FME Framework templates.
  • For Loading to a GSR view
  • For Loading to any other view
• 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.

RETURN TO TOP

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:
  1. The read-only staging area \\data.bcgov\data_staging_ro\BCGW\...
  2. 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

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:
  1. The read-only staging area \\data.bcgov\data_staging_ro\BCGW\...
  2. 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.

Next: Define the Source Reader User Parameters

Esri Shapefile (SHP)

• Common locations of source shapefiles are:
  1. The read-only staging area \\data.bcgov\data_staging_ro\BCGW\...
  2. 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

Next: Define the Source Reader User Parameters

GeographicJavaScript Object Notation (GeoJSON)

• Common locations of source GeoJSON files are:
  1. Operational systems, through a REST web service (e.g., https://gwells-prod.pathfinder.gov.bc.ca/gwells/api/v1/gis/wells)
  2. 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.

1. Create two readers, one for each FGDB
2. 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)
3. Create the parameters below with the following default values:
   • SRC_FEATURE_1 (Default Value=census_data2012)
   • SRC_FEATURE_2 (Default Value=census_data2013)
4. 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.

1. Create the reader for the FGDB that reads multiple feature types.

2. Rename the parameter “SourceDataset_FILEGDB” to SRC_DATASET_FGDB_1 (referring to CensusMultiYear.gdb).

3. 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.

1. 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 srcPass
  Link 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 connFile
  Link 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__.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., lrmbctsp.nrs.bcgov__DBP06.NRS.BCGOV.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.

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 srcPass
  Link 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 ecStr
  Link 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)
• Oracle Non-Spatial
• Oracle Spatial Object (SDO)

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:

1. Right click on the item you want to link from in the Navigator window.
2. Select Link to User Parameter
3. Select the associated user parameter name in drop down list
4. 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>___failedFeatures.ffs

• 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() to return 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:

1. Right click on the item you want to link from in the Navigator window.
2. Select Link to User Parameter
3. Select the associated user parameter name in drop down list
4. Click OK

• Log File (LOGFILE)
• File Change Detection (FILE_CHANGE_DETECTION)

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:

1. Expand Workspace Parameters and then Logging
2. Right click **Log File:**
3. Link to User Parameter… LOGFILE

Scripting → Startup Python Script

In the Navigator:

1. Expand Workspace Parameters and then Scripting
2. Double click **Startup Python Script:**
3. Copy and paste the text below:
4. Click OK

import fme
import DataBCFMWTemplate
start = DataBCFMWTemplate.Start(fme)
start.startup()

Scripting → Shutdown Python Script

In the Navigator:

1. Expand Workspace Parameters and then Scripting
2. Double click **Shutdown Python Script:**
3. Copy and paste the text below
4. 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.

RETURN TO TOP

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.

RETURN TO TOP

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

RETURN TO TOP

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:

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:

RETURN TO TOP

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:

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.

Illustration of Configuring an FME Script

RETURN TO TOP