Viewing and Inspecting SQL Server Data

Introduction

Connecting to a database in FME varies slightly from selecting a file or file/folder-based format. The process involves specific parameters related directly to the database.

The basic database connection parameters are:

• Server (Host) Name
• Network Port Number
• Database (Service) Name
• Username
• Password

These parameters may differ for each database format. Connecting to SQL Server requires only four of these parameters, it does not require a port number however it does require an authentication method.

Microsoft SQL Server offers two authentication methods: SQL Server Authentication and Windows Authentication. In SQL Server Authentication, credentials are created and stored in SQL Server. In Windows Authentication, identification is confirmed through the Windows principal token: username and password are not needed.
 

Video

Step-by-Step Instructions

Follow these steps to connect to the SQL Server database in FME. It is possible to connect to the database using two different methods, parameters for both methods are outlined in steps 2a and 2b.

1. Start FME Data Inspector

Select File > Open Dataset from the menu bar to open the selection dialog.

Set the Format to Microsoft SQL Server Spatial.

2. Define the Dataset

There are two methods to connect to database services, Embed Connections and Database Connections (also referred to as Named Connections). Embed connections require the user to enter the connection parameters each time a user would like to read from or write to a database. Database connections provide a secure and simple way to store and reuse connection parameters. With database connections, it is possible to select the saved connection when desired, rather than having to manually re-enter the connection parameters each time. For more information regarding database connections, please see Using Database Connections.

2a. Embed Connection

Select Embed Connection Parameters as the Connection, and open the Parameters. To connect to SQL Server, the following connection parameters are required:

• Server: sql.fmetraining.ca

• Database: fmedata

• Authentication: SQL Server Authentication

• Username: fmedata

• Password: SQLtraining2016

• Table List: fmedata2016.Neighborhoods

To locate the fmedata2016.Neighborhoods table, click the ellipsis to open the Select Table List window and choose the table.

The schema for this table is fmedata2016. Schemas are used to organize and manage tables, they can also provide a more efficient way to filter through tables in a list.

2b. Database Connection

Select Add Database Connection.

Once selected, FME will open the Add Database Connection parameters window. To connect to SQL Server for this tutorial, the following connection parameters are required:

• Name: SQL Training Database
• Server: sql.fmetraining.ca
• Database: fmedata
• Authentication: SQL Server Authentication
• Username: fmedata
• Password: SQLtraining2016

Click Save to close the connection parameters dialog. Click to open the Parameters… dialog otherwise FME will prompt you to check the parameters. Here we can select which table we want to inspect. Select fmedata2016.Neighborhoods by clicking the ellipsis button for the Select Table List window.

Finally, click OK on all dialogs to view the Neighborhoods table.

Result

The Neighborhoods table, both its geometry and attributes, is now displayed in Data Inspector.

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.