This parameter lets the user specify how features will be written into the destination table. Supported feature operations are:
Tip: Unlike in previous versions of FME, the fme_db_operation attribute will now cause feature rejection when Feature Operation is set to Insert, Update, or Delete.
This parameter controls how the feature type handles destination tables. These options are available:
When updating features, users have a choice to update, or skip, their spatial column(s). Possible options are:
When inserting into a table, Row Selection is ignored. When updating and deleting from a table, a condition needs to be specified for selecting which rows to operate on. This parameter group offers two methods to construct the selection condition.
Match Columns
If you select Match Columns, the columns specified in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.
WHERE Clause
If you select WHERE Clause, the corresponding WHERE Clause Builder will let the user construct a WHERE clause. The user can also type in a WHERE clause inline, without launching the WHERE Clause Builder. It is optional to start the clause with the keyword WHERE.
The WHERE Clause Builder makes it easy for users to reference feature attribute values, destination table columns, and invoke FME functions. The WHERE clause is first evaluated as an FME expression, before being passed onto the destination database.
If the WHERE clause is incorrect or if its evaluation results in failure, translation will fail. Otherwise, if the WHERE clause passes FME evaluation but it is SQL invalid, the feature will be rejected or the translation will fail.
For advanced users, conditional FME expressions created through the Conditional Value editor can be used to create WHERE clauses.
Tip: You can set the WHERE Clause to an attribute. This supports workspace migration and existing workflows involving fme_where. Direct support for fme_where has been deprecated. To advanced users who are accustomed to using fme_where, if Feature Operation is set to Update, Delete, or fme_db_operation, an fme_where attribute that conflicts with Match Columns or WHERE Clause will result in feature rejection.
This parameter determines whether the column specified by Spatial Column contains Geometry or Geography values. Inherit From Writer causes the feature type to retrieve the value of Spatial Type from the writer parameter Spatial Type.
This parameter specifies the name of a spatial column. If this parameter is left empty, it will inherit from the writer parameter Spatial Column.
The parameters in this section take effect only when FME creates a table.
This parameter specifies how a spatial index is to be created for the specified Spatial Column.
Note: Azure SQL Database does not yet support the Auto mode.
Due to the complexity of spatial index creation, FME lets the user specify a SQL statement to create an index on a specific spatial column in a Microsoft SQL Server Spatial table. Normally, this table would be the destination table specified by the feature type.
In the example below, a default spatial index is created on geometry column GEOM of table my_table:
CREATE SPATIAL INDEX SIDX_GEOM ON my_table(GEOM) WITH (BOUNDING_BOX = (-200, -200, 200, 200))
In the example below, a spatial index is created on geography spatial column GEOG of my_table:
CREATE SPATIAL INDEX [SIDX_GEOG] ON my_table(GEOG) USING GEOGRAPHY_GRID WITH (GRIDS = (LOW, MEDIUM, HIGH, HIGH), CELLS_PER_OBJECT = 32)
Tip: To recreate a spatial index on an existing table, execute a SQL statement inside a SQLCreator Workbench transformer. Using the above example, the SQL statement would look like:
CREATE SPATIAL INDEX [SIDX_GEOG] ON my_table(GEOG) USING GEOGRAPHY_GRID WITH (GRIDS = (LOW, MEDIUM, HIGH, HIGH), CELLS_PER_OBJECT = 32, DROP_EXISTING=ON)
See the MSDN article CREATE SPATIAL INDEX (Transact-SQL) for more information and examples.
These parameters together specify a bounding box for geometries, and optionally for geographies, for spatial index creation.
This parameter determines how FME aggregates and multi's will be interpreted and handled. Because there is not a one to one mapping between FME collections and SQL Server collections, care should be taken when input features contain spatial collections.
The option Preserve Collection Types will interpret FME multi's as OGC multi's when such an interpretation is geometrically lossless, and as OGC GeometryCollections otherwise. This option preserves the collection type only. It does not promise that the resulting OGC multi or GeometryCollection will be simple or valid.
The option Simplify Collection Types will interpret homogeneous FME collections as OGC multi's when such an interpretation is geometrically lossless, and GeometryCollections otherwise. It does not promise that the resulting OGC multi or OGC GeometryCollection will be simple or valid. Homogeneous FME collections refer to IFMEMulti's, and single level IFMEAggregates that can be converted into IFMEMulti's without loss of geometry.
The option Simplify To Valid Collection Types will interpret homogeneous FME collections as OGC multi's when such an interpretation is geometrically lossless, and additionally when these OGC multi’s are SQL Server valid. FME collections that are SQL Server invalid are interpreted as GeometryCollections. Further:
All three options are recursive; they apply to nested FME collections.
When the writer parameter Handle Multiple Spatial is set to No, Multiple Geometries are treated as FME collections. When Handle Multiple Spatial is set to Yes, the parts of a top-level Multiple Geometry are interpreted as distinct spatial columns, and each of these parts are handled separately.
When inserting features, this parameter determines whether or not identity columns are auto-populated. This parameter does not affect other feature operations.
There can be at most one identity column specified per table. If one has been specified, the option Yes will cause the destination identity column to be populated with values from the feature attribute that corresponds to the identity column. The option No will cause the destination identity column to be auto populated by the database server.
Note: Only one feature type may have this option enabled for a given MSSQL connection; all feature types within a writer share a connection, and connections may be shared between writers if they use the same connection parameters.