Converting from SQL Server

Introduction

With FME, reading from a database is comparable to reading from a file-based dataset. Various FME parameters exist to provide control when reading a spatial database. These parameters can control the Reader (Database) and the Feature Types (Tables) to improve database reading performance. When reading from a database, not every feature in every table is required; the fewer features that are read from the database, the quicker the read will be, the less system resources will be used, and the faster the overall translation will be.

The following may improve Microsoft SQL Server database reading performance:

Command Timeout: The length of time, in seconds, for the database to return a result before termination. If set to 0, there will be no timeout. The default value is 30 seconds.
WHERE Clause: Specifies a query, so that only features that pass the query will be returned to FME. This employs database query tools, which in turn utilize database indices, and is significantly more efficient than reading an entire table and then filtering it with a Tester transformer.
Number of Records to Fetch At A Time: Defines how many records will be fetched from the database at a time. Too low a number and FME will spend excessive time making read requests. A too high number can slow down database performance for other users. The number of records is set in the Navigator window under the Reader parameters; the default value is 10.
Search Envelope: Set a spatial query; only features that fall inside the specified extents will be returned to FME. Again, this employs native database functionality and is more efficient than reading the entire table and then clipping it with a Clipper transformer. An optional “Clip to Search Envelope” parameter defines whether features will be clipped where they cross the defined extents, or be allowed to pass completely where at least a part of them falls inside the extents. The limitation here is that the four parameters only define a rectangular envelope. Note that to use a search envelope requires a spatial index to exist for that table; FME can’t do a spatial query without one. If a format doesn’t support a spatial index being created on a view, then FME will not be able to do spatial queries on that view.
FeatureReader transformer: The FeatureReader reads data from a database and uses incoming features as the basis for a spatial or non-spatial query.

Note: The Search Envelope is only available for Microsoft SQL Server Spatial Readers; non-spatial readers will not have this option, as this format does not support a spatial index.

Starting in FME 2025.2, many formats that support both spatial and non-spatial data have been combined into a single reader or writer. For an up-to-date list of the formats this change has been applied to, please see Combined Spatial and Non-Spatial Readers and Writers.

Video

Step-by-Step Instructions

In this demonstration, a WHERE clause will be used to select only the park features in a chosen neighborhood of interest. The Parks table already exists in the SQL Server database and will be written to a File Geodatabase.

1. Create New Workspace and Add SQL Server Connection

Start FME Workbench. From the Start page, select the New Workspace option.

To create a new Database connection, click on Utilities > FME Options. Select Database connections and click on the + arrow to create a new connection. Enter the following information:

Database Connection: Microsoft SQL Server
Name: SQL Training
Server: sql.fmetraining.ca
Username: fmedata
Password: SQLtraining2016
Database: fmedata

From the Readers menu, select Add Reader and set the following:

Format: Microsoft SQL Server (MSSQL)
Connection: SQL Training
- This is the name of the connection created above

Click Paramaeters

In the parameters window that opens, click on the ellipsis next to the Tables field. Select from fmedata2016 the Parks table and click OK

Click OK twice to add the reader to the canvas

2. Set the WHERE Clause

In the reader in the previous step, there was a field to enter the WHERE clause. We can also add this clause through the Navigator pane. Right-click on the WHERE clause parameter and select Edit Parameter Value.

Type the following Clause into the area provided in the SQL Server Parameters dialog box.

WHERE Clause: "NeighborhoodName" = 'Fairview'

In standard SQL, double quotation marks indicate an attribute/field name (NeighborhoodName), while single quotation marks indicate an attribute/string value (Fairview).

Click OK to accept the parameters.

Click on the fmedata2016.Parks reader to bring up the mini toolbar. Click the icon with the eyeball to view the data in the Data Preview pane.

Upon inspecting the data, we can see in the Visual Preview that there are only parks in the Fairview neighborhood.

3. Add the File Geodatabase Writer

From the Writers menu, select Add Writer. Set the following parameters:

Format: Esri Geodatabase (File Geodb Open API)
Dataset: SQLTutorial.gdb
- Click on the ellipses to navigate to a location on your computer

Click OK

In the Feature Type window that opens, set the following:

Feature Class or Table Name: Parks

Click OK to add the writer to the canvas.

Connect the Reader and Writer. Your canvas should resemble the image below.

4. Run the Workspace

From the toolbar, click the green arrow to run the workspace and inspect the results.

Maintaining the attributes from the SQL Server Reader, the File Geodatabase Writer writes 10 parks in the Fairview neighborhood to Parks.gdb

Advanced Option

Using Concatenated Published Parameters

An issue with the WHERE clause parameter (and similar parameters) is that it can be difficult to get user input and apply it to the clause. Publishing the parameters is not helpful, as the user would still have to enter the full clause (<field> = <value>). Often, only the <value> part is required as input. A concatenated parameter is useful in these scenarios. It is a parameter composed of a constant string (<field>) and a user-defined value (<value>).

1. Add a Published Parameter

From the Navigator pane, right-click on "User Parameters" and choose Manage User Parameter.

In the Add/Edit User Parameter window, click on the green plus arrow and select Text from the drop-down to add a text user parameter.

Set the following for the new user parameter:

Parameter Identifier: NeighbourhoodName
Label: NeighborhoodName:
Value Assignment:
- Default Value: 'Fairview'

Click OK to accept the user parameter values.

2. Convert the WHERE Clause to a Published Parameter

From the Navigator pane, expand the reader Parameters. Right-click on the WHERE Clause, and choose Create User Parameter.

Set the following for the new user parameter:

Label: WHERE Clause:
Visibility: Always Hide
Default Value: "NeighborhoodName" = $(NeighborhoodName)
- This parameter refers to the previously created published parameter, NeighborhoodName.

Click OK.

3. Run the Workspace using Run with Prompt

Before running the workspace, ensure that Enable Feature Caching, Prompt for User Parameters, and Redirect Writers to Data Inspection are enabled. This will prompt the user to enter a neighborhood name and then display the output in the Inspector. Selecting the redirect option is useful to display outputs before writing to the Writer, especially when writing to a database.

Run the workspace. When prompted, enter a valid NeighborhoodName ('Downtown', 'West End', 'Kitsilano', 'Mount Pleasant', 'Strathcona', or 'Fairview') into the field provided. Note that single quotation marks indicate an attribute/string value and are required for attribute values.

View the results in the Data Preview. Below you can see the results for 'West End'.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.

Search