Microsoft SQL Server Spatial Writer Feature Type Parameters
To access feature type parameters, click the gear icon Tip To always display the editor in FME Workbench, you can select View > Windows > Parameter Editor.
General All feature types share similar General parameters, which may include Feature Type Name, Reader or Writer information, and Geometry. In most Writer Feature Type parameter dialogs, you can also control Dynamic Schema Definitions. Some database formats accept Table or Index Qualifier prefixes on the output table feature type. |
Table Settings: General

This parameter specifies how features will be written into the destination table. Supported feature operations are described below. Note that the described behavior can be dependent on the selected options, as well as the underlying table properties.
- More information about Feature Operations.
Option |
Description |
If the Row Does Not Exist |
If the Row Exists |
Insert |
The writer appends a new row to a table using input feature attributes. |
The writer creates a new row using input feature attributes. |
Not always applicable: if the table does not have a unique key or it has an automatically generated unique key, insertion is always possible. The database cannot violate its key constraints; therefore, errors can occur on row insertion. For example, if there is a unique key and a user specifies the value with the feature, and the feature already exists, then FME Workbench logs an error. This error might be in the form of a rejected feature, or the database may stop processing altogether. |
Update |
The writer updates existing row(s) in a table using input feature attributes. A selection method must be specified in the Row Selection group. |
The writer rejects the input feature or logs an error if it is unable to continue. |
The writer only changes values of the existing row(s) corresponding to the input feature that differ from the input feature. |
Upsert |
The writer appends a new row or updates an existing row(s) using input feature attributes and/or geometries. A selection method must be specified in the Row Selection group. |
The writer creates a new row using input feature attributes and/or geometries. |
The writer changes values of the existing row(s) corresponding to the input feature that differ from the input feature. It leaves other attributes untouched. |
Delete |
The writer deletes an existing row(s) in a table. A selection method must be specified in the Row Selection group. |
The writer rejects the input feature or logs an error if it is unable to continue. |
The writer deletes existing row(s) corresponding to the input feature. |
fme_db_ |
The feature operation will be determined by the attribute fme_db_operation on each input feature. A selection method must be specified in the Row Selection group. The value of fme_db_operation will be processed according to the steps below. Note The processing steps listed below depend on a format's available Feature Operation options.
Note about earlier FME versions: To use fme_db_operation, you must set Feature Operation to this option. In previous versions of FME, you could set fme_db_operation when the destination feature type was set to Insert, Update, Upsert, or Delete. Doing this now will cause feature rejection. |
The action depends on the operation; however, in general, if nothing is specified, the value is treated as an Insert. | The value is treated as an Insert. |

Controls how the feature type handles destination tables:
- Use Existing – Write to an existing table If the destination table does not exist, the translation will fail.
- Create If Needed – Create the destination table if it does not exist.
- Drop and Create – (This option is not available in all formats.) Drop the destination table if it exists, and then create it. The writer will drop and re-create the table before writing any features to it. Tables will be overwritten when the first input feature is processed. If no features are sent to a feature type, then the corresponding table will not be overwritten.
- Truncate Existing – (This option is not available in all formats.) If the destination table does not exist, the translation will fail. Otherwise, delete all rows from the existing table.

When updating features, users have a choice to update, or skip, their spatial column(s). Possible options are:
- Yes: The spatial column(s) specified by the user will be updated. IFMENulls will be written as null values and replace existing spatial values.
- No: No spatial columns will be updated.

Feature Operation Option |
Row Selection |
---|---|
Insert |
Row Selection is ignored. |
Update, Upsert, Delete (Options may differ depending on a format's available Feature Operation options.) |
A Row Selection 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
The columns specified in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update, upsert, or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.
WHERE Clause
This parameter opens a WHERE Clause Builder. You can also type a WHERE clause inline, without launching the Builder. It is optional to start the clause with the word 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, the 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.
Table Creation

This parameter specifies how a spatial index is to be created for the specified Spatial Column.
- None: No spatial index will be created for this table.
- Manual: The SQL statement specified for the Spatial Index Creation SQL parameter will be executed.
- Auto: A spatial index will be created automatically for geographies, and semi-automatically for geometries. This automation is available only on SQL Server 2012 and later with database compatibility mode of SQL Server 2012 or higher.
- Geographies: No other information is required. A bounding box can be optionally specified.
- Geometries: A bounding box is required for spatial index creation. The bounding box is specified in Bounding Box (Geometry Only).

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)
To recreate a spatial index on an existing table, execute a SQL statement inside a SQLCreator FME 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.
- Minimum X: The x-coordinate of the bottom left corner of the bounding box.
- Minimum Y: The y-coordinate of the bottom left corner of the bounding box.
- Maximum X: The x-coordinate of the top right corner of the bounding box.
- Maximum Y: The y-coordinate of the top right corner of the bounding box.
Table Settings: Spatial

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.
Table Settings: Advanced

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:
- This option will have the effect of repairing some homogeneous collections that are SQL Server invalid. For example, if an IFMEMultiArea contains overlapping polygons that are individually SQL Server valid, then interpreting such an IFMEMultiArea as a GeometryCollection will remove the invalidity.
- This option is unable to repair homogeneous FME collections whose parts are individually SQL Server invalid.
- This option considers destination spatial column types. An FME collection that is SQL Server valid as a geometry may not be SQL Server valid as a geography, and vice-versa.
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.
Identity columns are columns whose values are automatically populated by the database server in sequence. This parameter allows you to override this behavior and provide the values.
There can be, at most, one identity column specified per table.
- If an identity column has been specified, the option Yes populates the destination identity column with values from the feature attribute that corresponds to the identity column.
- The option No causes the destination identity column to be auto-populated by the database server.
This parameter does not affect other feature operations.
For more information on Identity Columns, see the Microsoft Developer Network topic IDENTITY (Property) (Transact-SQL).