Skip To Content

Connect to external data

The ArcGIS Maps Connect workflow supports external content from Microsoft SQL Server 2008 R2, 2012, 2012 R2, and 2014, including the SQL Server Express editions. The external content must contain data that can be geocoded, such as an address, U.S. city, U.S. state, ZIP code, or world city. The external content must also contain a primary key column. Alternatively, the table can contain an existing SQL server spatial data type (geography or geometry) column that is then converted by the ArcGIS Maps Connect workflow for use in ArcGIS Maps for SharePoint. If the external table has an existing spatial column that contains no data, the ArcGIS Maps Connect workflow populates the column based on other location information in the table (for example, address). If no spatial column exists, the ArcGIS 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 enriched with additional geographic data variables from ArcGIS.

Note:

If the ArcGIS Maps Connect workflow fails, ensure the appropriate permissions for Microsoft SQL Server have been set. You can view the error messages in the SharePoint site workflow history to view exact details on the settings that need to be corrected.

When the ArcGIS 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 ArcGIS Maps Locate workflow and geoenrichment.

Prerequisites

Before you can use the ArcGIS Maps Connect workflow, the following prerequisites must be met:

  • Credentials for an ArcGIS organizational account must be properly configured for ArcGIS Maps for SharePoint.
  • Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012, Microsoft SQL Server 2012 R2, or Microsoft SQL Server 2014 (including Microsoft SQL Server Express)
  • A primary key or unique field in the external table—The ArcGIS 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.
  • Communication between the ArcGIS Maps Connect workflow and the external SQL Server tables occurs within the SharePoint web application pool account. This account is used to connect to an external table, and you must set the appropriate permissions on that account to enable the connection. The application pool account is usually a domain account. If, however, the account is a NT AUTHORITY\Network Service account (local built-in account), which is typically the case when the SharePoint is installed using the stand-alone mode, you must grant appropriate permissions for the account. When the NT AUTHORITY\Network Service account is the web application pool account, that account's credentials are used to authenticate connection to the database if the database is hosted in an SQL server that runs on a different server from the one running SharePoint in the same domain (or in a trusted domain). The Network Service account's credentials are in the form DomainName\SharePointServer$, where DomainName is the domain of your SharePoint server and SharePointServer is the name of your SharePoint server. You must grant appropriate permissions for the DomainName\SharePointServer$ account.

    Configure the following for the SharePoint web application pool account (or NT AUTHORITY\Network Service or DomainName\SharePointServer$ account, depending on your enrvironment) and the WSS Content Application Pools database role.

    • Create a server login for the SQL Server instance for the account, if one does not already exist.
    • Map this login to a database user for the database that contains the external table to which you want to connect. Grant the database user the following permissions:
      • db_datareader
      • db_datawriter
      • db_ddladmin
    • Add the following stored procedures to the securables list for the WSS Content Application Pools database role in the farm configuration database, SharePoint Config. Grant the WSS Content Application Pools database role the execute permission on those stored procedures:
      • proc_putObject
      • proc_putObjectTVP
      • proc_putClass
      • proc_getNewObjects

Troubleshooting

The following are some common errors and solutions for resolving them.

  • Error—The EXECUTE permission was denied on the object 'proc_putObjectTVP', database 'SharePoint_Config', schema 'dbo'.

    Solution—Grant execute permission on the procedures stored in the farm configuration database to the WSS_Content_Application_Pools database, as decribed above.

  • Error— Access to the path 'C:\ProgramData\Microsoft\SharePoint\Config\xxx\' is denied.

    Solution— Grant full control permission to the SharePoint web application pool account on the directory in question.

Start the ArcGIS Maps Connect workflow

The first part of the ArcGIS Maps Connect workflow involves creating a connection to your external SQL Server table. You must complete these steps before you can spatially enable (geocode) and geoenrich your list items.

To start the ArcGIS Maps Connect workflow, do the following:

  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 ArcGIS Maps Connect to start the workflow.

    The ArcGIS Maps Connect workflow page appears.

  3. If this is the first time you are using the ArcGIS 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.

    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 ArcGIS Maps Connect workflow.

Geocode and enrich your data

The second part of the ArcGIS Maps Connect workflow covers geocoding (spatially enabling) and optionally enriching your data with additional geographic variables. This part of the workflow is identical to the ArcGIS Maps Locate workflow used to geocode existing SharePoint lists.

  1. Specify how location is represented in your data.

    Available choices are: Address, U.S. City, U.S. State, U.S. ZIP Code, World City, and Country.

    To add a custom location type, click Add location type.

  2. Click Next.
  3. Choose the columns in the list that correspond to the input parameters and click Next.
  4. Specify which columns to add to the new connected SharePoint list and click Next.
  5. You can optionally enrich your data with geographic data enrichment variables from ArcGIS. Data enrichment variables are contextual variables available for locations throughout the U.S., Canada, and some countries in Western Europe. Available variables include demographic and socioeconomic factors, age distributions, landscape data, and area wealth information.

    Note:

    Geoenrichment capabilities are supported only with ArcGIS Online , Portal for ArcGIS 10.3 through 10.4.1, and ArcGIS Enterprise 10.5 and later.

    To enrich your data, do the following:

    1. Check the Enrich data box and click Next.

      The data enrichment pane opens.

      Geographic data enrichment collection

    2. Click the country drop-down menu and choose the country for which you want to see demographics.

      The list of collections changes depending on the available collections for the selected country.

    3. Optionally, type keywords in the search field to search for specific variables. Press Enter or click the magnifying glass to search.
    4. Choose a data collection.

      You can search for variables within a collection, choose one of the popular variables within the collection, or show all variables in the collection.

    5. Choose the variables you want to add to your list and click Next.

      Note:

      The basket icon in the upper right corner of the window shows the number of variables you've chosen. Click the basket to view its contents. To remove a variable, click the X beside its name.

    6. The summary window shows a summary of the selected data collections, the type of areas that will be enriched, the number of variables selected, and the number of ArcGIS service credits you'll be charged based on the number of currently selected variables. To add or remove individual variables, expand the data collections, and place a check mark in the boxes beside the variables you want to include.

      • By default, for point layers, data will be returned for a 1-mile radius surrounding each location. To change the radius or to use a drive time or drive distance value instead, click edit and make the appropriate changes.
      • Map layers containing polygons will return results for the area within each polygon.

    7. Click Add data to system.

      The data variables append to your existing SharePoint list and also display in feature pop-ups on the map.

  6. Click Start workflow to finish.