The directives processed by the Microsoft SQL Server 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 writer is MSSQL_ADO.
DATASET, SERVER, USER_NAME, PASSWORD, USE_SSPI, ENCRYPT_CONNECTION, COMMAND_TIMEOUT
These directives operate in the same manner as they do for the Microsoft SQL Server Reader. The remaining writer-specific directives are discussed in the following sections.
START_TRANSACTION
Required/Optional: Optional
This statement tells the Microsoft SQL Server writer module when to start actually writing features into the database. The Microsoft SQL Server 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 |
Example:
MSSQL_ADO_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 MSSQL_ADO_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 |
If the MSSQL_ADO_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. 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_ADO_TRANSACTION_INTERVAL 5000
Workbench Parameter: Transaction interval
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 or writer (depending on where the SQL statement is executed) 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.
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 or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
End SQL