This is an archive related to a previous version of Esri Maps for SharePoint. If you need the current version go to http://doc.arcgis.com/en/maps-for-sharepoint/.

Connect to external data

Business data often resides in more than just one place. Esri Maps for SharePoint leverages SharePoint's Business Connectivity Services (BCS) to retrieve data that resides in Microsoft SQL Server (including SQL Server Express) and pull it in to SharePoint. BCS creates an external content type in SharePoint that allows for read and write access to the connected table. This means that not only can SQL Server external content be added to a connected list in SharePoint, changes made to the list by the Esri Maps Connect workflow, including location information and enriched attributes, can be written back to the original table.

The Esri Maps Connect workflow walks you through the process of connecting to your SQL Server database, selecting the applicable table, creating the connected list, spatially enabling the list, and enriching the list with demographic and other contextual data, all with no custom code or programming. Once the connected list has been created, the list data can be added to the ArcGIS Map Web Part and the Esri Maps Web Part.

The Esri Maps Connect workflow creates a SharePoint list that maintains a connection to the underlying SQL server database. The SharePoint Business Connectivity Service (BCS) uses SharePoint timer jobs to maintain and update the connection between the SQL Server database and the connected SharePoint list. By default, the timer job runs once every hour, but this setting can be changed in the SharePoint Central Administration.

When the Esri Maps Connect workflow completes, the result is a regular SharePoint list, not an external list. That said, the fields created from the SQL Server database are of an external type, and edits made to these fields in SharePoint cannot be passed back to the database. SharePoint can only pass back the fields it has created, such as for the Esri Maps Locate workflow and GeoEnrichment.

The Esri Maps Connect workflow supports external content from Microsoft SQL Server 2008 R2, 2012, and 2014, including the SQL Server Express editions. The external content must contain data that can be spatially enabled (geocoded), such as an address, U.S. city, U.S. state, ZIP code, or world city. Alternatively, the table can contain an existing SQL server spatial data type (geography or geometry) column that is then converted by the Esri Maps Connect workflow for use in Esri Maps for SharePoint. If the external table has an existing spatial column that contains no data, the Esri Maps Connect workflow populates the column based on other location information in the table (for example, address). If no spatial column exists, the Esri Maps Connect workflow creates a geography spatial type column named EsriShape with a Spatial Reference Identifier (SRID) of 4326 (WGS 84). The EsriShape field supports all geometries including points, lines, and polygons. In all scenarios, the external content can be geoenriched with additional geographic data variables from ArcGIS.

The Esri Maps Connect workflow requires the following prerequisites:

  • An ArcGIS organizational account
  • Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2012 or Microsoft SQL Server 2014 (including Microsoft SQL Server Express)
    • An NT AUTHORITY\ANONYMOUS LOGON or NT AUTHORITY\IUSR login. See Create a login for more information.
    • A database user for the applicable database with a minimum permission of db_datareader. The NT AUTHORITY\ANONYMOUS LOGON or NT AUTHORITY\IUSR login should be mapped to this login. See Create a database user for more information.
    • Update/insert/select permission on the custom schema to the database associated with the [NT AUTHORITY\NETWORK SERVICE] account.
    • Update/insert/select permission on the SQL Server table to the database user associated with the [NT AUTHORITY\NETWORK SERVICE] account.
    • The SharePoint server machine must be configured as follows:
      • If the SharePoint server machine is the same server machine that hosts the SQL server, the NT AUTHORITY\NETWORK SERVICE account should be granted at least db_datareader/db_datawriter/db_ddladmin permission.
      • If the SharePoint server machine is a different server machine than the one that hosts SQL server, the [domain name]\[SharePoint server machine name]$ (for example, DOMAIN\SPMACHINE$) account should be granted at least db_datareader/db_datawriter/db_ddladmin permission.
  • SQL Server tables created with a DBO schema.
  • A primary key or unique field in the external table—The Esri Maps Connect workflow requires that the external data source contains a unique field. Ideally, this is a field that is set as the primary key. It is often easiest to add an integer column to your existing table that autoincrements (that is, an Identity column). If a primary key is not or cannot be set, you can use any field that has unique values.

The following steps describe the first part of the Esri Maps Connect workflow and cover signing in to ArcGIS and creating a connection to your external content. You must complete these steps before you can spatially enable (geocode) and geoenrich your list items.

  1. Browse to the Site Contents page in your SharePoint site collection and click Site Workflows.
  2. In the Start a New Workflow section, click Esri Maps Connect to start the workflow.

    The Esri Maps Connect workflow appears.

  3. If this is the first time you are using the Esri Maps Connect workflow, choose Create new under Specify the external content type.

    When the workflow completes successfully, the external content type is saved to the site collection, allowing you to reuse the external content type, if necessary.

    External content types are a key feature of SharePoint's Business Connectivity Services that store descriptions of connectivity information and data definitions. For detailed information, see External content types in SharePoint 2013.

    1. Under Connect to database, specify whether to connect using a new database connection or an existing database connection (if you have previously completed the workflow). If you are creating a new connection, enter the database server and name and click Connect.

      For the database server, enter <server name>\<instance>. For the database name, enter the name of the applicable database. You will select a table from the database in the next step.

    2. Under Select database table and columns, use the drop-down menus to specify the database table, primary key column (or unique field), and optionally, spatial column. The spatial column drop-down menu automatically populates if the workflow detects an existing spatial data type (geography or geometry) column in your database.
    3. Specify a name for the external content type. The information you have completed in the previous steps are saved to an external content type in your site collection with the name you specify here.
  4. Specify a name for displaying the connected list in SharePoint
  5. Specify how to locate list items.
    • Use locations from the spatial column—This option is only available if your database table contains a spatial column.
    • Locate items based on information from other columns—Use this option to locate your list items based on an Address, U.S. City, U.S. State, ZIP Code, or World City.
  6. Click Next to move to the next page of the Esri Maps Connect workflow.

    The next set of steps describe the second part of the Esri Maps Connect workflow and cover spatially enabling (geocoding) and optionally enriching your data with additional geographic variables. This part of the Esri Maps Connect workflow is identical to the Esri Maps Locate workflow used to spatially enable existing SharePoint lists.

  7. Specify how location is represented in your data. The choices are Address, U.S. City, U.S. State, US ZIP Code, World City, and Country. Click Next.
  8. Choose the columns with the location information—Specify the data in your table with the location information. Click Next.
  9. Choose the columns to add—Specify which columns should be added to the SharePoint list that is created. Click Next.
  10. You can optionally enrich your data with geographic data enrichment variables from ArcGIS. To do this, check the check box and click Next; otherwise, leave the box unchecked and skip to the final step. Note that GeoEnrichment capabilities are supported with ArcGIS Online only.
    1. Select the data collection or collections and click Next.
    2. Specify whether you want to see data available globally or for a specific country. Once you have selected a country, select the data variables and click Next.
    3. Review your selected variables and credits. Change the distance buffer to rings, drive times, or distances, if necessary. Click Add data to system to finish.
  11. Click Start workflow to finish.