Writer Directives

The directives processed by the Microsoft SQL Server Spatial Writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the Microsoft SQL Server Spatial writer is MSSQL_SPATIAL.

SERVER

Required/Optional: Required

The host name of the Microsoft SQL Server.

MSSQL_SPATIAL_SERVER mi6

Workbench Parameter: Server

USE_SSPI

Required/Optional: Optional

This specifies whether Windows Authentication should be used to authenticate to the database server. This directive should be set to either yes or no. The default is no.

If USE_SSPI is set to yes, then Windows Authentication is used, and the USER_NAME and PASSWORD directives are ignored.

MSSQL_ADO_USE_SSPI yes

Workbench Parameter: Use Windows Authentication

ENCRYPT_CONNECTION

Required/Optional

Optional

Mapping File Syntax

MSSQL_ADO_ENCRYPT_CONNECTION yes

Workbench Parameter

Encrypt Connection

USER_NAME

Required/Optional: Optional

The name of user who will access the database. If Windows Authentication is being used, this is ignored.

MSSQL_SPATIAL_USER_NAME bond007

Workbench Parameter: User Name

PASSWORD

Required/Optional: Optional

The password of the user accessing the database. If Windows Authentication is being used, this is ignored.

MSSQL_SPATIAL_PASSWORD moneypenny

Workbench Parameter: Password

COMMAND_TIMEOUT

The timeout in seconds for a query to the database. If set to zero, there is no timeout. The default is 30.

Required/Optional

Optional

Values

0 = no timeout

Default: 30

Mapping File Syntax

MSSQL_SPATIAL_COMMAND_TIMEOUT 15

Workbench Parameter

Command Timeout

DATASET

Required/Optional: Required

This is the database name.

Example:

MSSQL_SPATIAL_DATASET citySource

Workbench Parameter: Destination Microsoft SQL Server Spatial Name

DEF

Required/Optional: Required

See Table Representation for details.

BULK_INSERT

Required/Optional

Optional

The BULK_INSERT option changes the insert mode from feature by feature to batch. This can result in anywhere from six to ten times greater insertion speed. The compromise is reduced granularity in errors, with one invalid feature potentially causing the failure of a complete transaction.

Note: The speed of the bulk insert will increase with transaction size (fewer transactions = faster insert). Larger transaction sizes also mean that if a transaction fails there will be more rows to re-insert.

In Azure, Bulk_Insert is very important as the insertion speed increase is on the order of 300x. However, for Bulk mode to work with Azure, the SERVER_NAME and USER_NAME parameters must be changed.

SERVER_NAME = tcp:SERVERNAME.database.windows.net

USER_NAME = USERNAME@SERVERNAME

The remaining parameters can be left unchanged.

If the BULK_INSERT statement is not specified, then a value of NO is given. BULK_INSERT can be set to YES or NO.

Workbench Parameter

Bulk Insert

BEGIN_SQL{n}

Sometimes, you must execute some SQL statements prior to opening a table. For example, it may be necessary to ensure that a view exists before attempting to read from it.

Upon opening a connection to read from a database, the reader looks for the directive <ReaderKeyword>_BEGIN_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

Begin SQL

END_SQL{n}

Sometimes you must execute some SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it in BEGIN_SQL.

Just before closing a connection on a database, the reader looks for the directive <ReaderKeyword>_END_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Required/Optional

Optional

Workbench Parameter

End SQL

START_TRANSACTION

Required/Optional: Optional

This statement tells the Microsoft SQL Server Spatial writer module when to start actually writing features into the database. The Microsoft SQL Server Spatial writer does not write any features until the feature is reached that belongs to <last successful transaction> + 1. Specifying a value of zero causes every feature to be output. Normally, the value specified is zero – a non-zero value is only specified when a data load operation is being resumed after failing partway through.

Parameter	Contents
<last successful transaction>	The transaction number of the last successful transaction. When loading data for the first time, set this value to 0. Default: 0

Parameter

Contents

The transaction number of the last successful transaction. When loading data for the first time, set this value to 0.

Default: 0

Example:

MSSQL_SPATIAL_START_TRANSACTION 0

Workbench Parameter: Start transaction at

TRANSACTION_INTERVAL

Required/Optional: Optional

This statement informs the FME about the number of features to be placed in each transaction before a transaction is committed to the database.

If the TRANSACTION_INTERVAL statement is not specified, then a value of 500 is used as the transaction interval.

Parameter	Contents
<transaction_interval>	The number of features in a single transaction. Default: 500

Parameter

Contents

<transaction_interval>

The number of features in a single transaction.

Default: 500

If the TRANSACTION_INTERVAL is set to zero, then feature based transactions are used. As each feature is processed by the writer, they are checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or rollback the current transaction. The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.

Specifically, the value of fme_db_transaction can be one of:

COMMIT_BEFORE: The current transaction is committed before adding the feature to a new transaction.
COMMIT_AFTER: Add the feature to the current transaction, then commit the transaction.
ROLLBACK_AFTER: Add the feature to the current transaction, then rollback the transaction. That is, no feature in the current transaction will be committed.
IGNORE: The feature is added to the current transaction.

When the attribute is not found on the feature, then a value of IGNORE is assumed.

Example:

MSSQL_SPATIAL_TRANSACTION_INTERVAL 5000

Workbench Parameter: Transaction interval

SPATIAL_TYPE

Required/Optional: Optional

This directive specifies whether to write geometry (planar data) or geography (geodetic data) when writing to tables whose DEF line does not include the mssql_geom_column or mssql_geog_column directives.

This directive only has effect in combination with the SPATIAL_COLUMN directive.

Parameter	Contents
<spatial_type>	“geometry” or “geography” Default: geometry

Parameter

Contents

<spatial_type>

“geometry” or “geography”

Default: geometry

Example:

MSSQL_SPATIAL_SPATIAL_TYPE geometry

Workbench Parameter: Spatial Type

SPATIAL_COLUMN

Required/Optional: Optional

This directive specifies the geometry or geography column to use when writing to tables whose DEF line does not include the mssql_geom_column or mssql_geog_column directives.

This directive only has effect in combination with the SPATIAL_TYPE directive.

Parameter	Contents
<spatial_column>	The spatial column name. Default: GEOM

Parameter

Contents

<spatial_column>

The spatial column name.

Default: GEOM

Example:

MSSQL_SPATIAL_SPATIAL_COLUMN GEOM

Workbench Parameter: Spatial Column

ORIENT_POLYGONS

Required/Optional: Optional

When writing geography (geodetic) data, polygons must be oriented according to the left-hand rule: outer boundaries must be counter-clockwise and inner boundaries must be clockwise. If this directive is set to “NO”, FME will not automatically reorient polygons. You may wish to disable this feature if your input polygons are known to have correct orientation. Note that FME determines polygon orientation by projecting features onto a plane which does not wrap around the earth’s poles or 180 degree meridian and does not take into account the curvature of the earth. Therefore, FME may (re)orient polygons incorrectly in some cases.

Parameter	Contents
<reorient_polygons>	“YES” or “NO” Default: YES

Parameter

Contents

<reorient_polygons>

“YES” or “NO”

Default: YES

Example:

MSSQL_SPATIAL_ORIENT_POLYGONS YES

The Mapping File Directives sections should now only include

Directive name

Optional/Required

Values (including default)

Workbench Parameter: Orient Polygons

HANDLE_MULTIPLE_SPATIAL_COLUMNS

If this directive is set to YES, feature geometry will be written from an aggregate.

This aggregate must contain individual geometries, namely that each part is independent from the others and is its own complete geometry. Each part geometry of the aggregate must have a name. If the aggregate contains geometries with names that match the spatial columns of the table being written, the geometries will be written to the appropriate columns.

When using this feature, the geometry/geography columns cannot be specified.

Required/Optional

Optional

Mapping File Syntax

<WriterKeyword>_HANDLE_MULTIPLE_SPATIAL_COLUMNS YES

Workbench Parameter

Handle Multiple Spatial Columns